Mysql Question posted in
The official MySQL documentation can be found here.

Xpath 1.0 Mysql issue

I have this sample xml

<ProductSaleItem>
  <ItemReference>100</ItemReference>
  <Discount>
    <Modifier>1</Modifier>
  </Discount>
</ProductSaleItem>
<ProductSaleItem>
  <ItemReference>101</ItemReference>
  <Discount>
    <Modifier>1</Modifier>
  </Discount>
  <Discount>
    <Modifier>2</Modifier>
  </Discount>
</ProductSaleItem>
<ProductSaleItem>
  <ItemReference>102</ItemReference>
</ProductSaleItem>

and this xpath

//ProductSaleItem[Discount/Modifier]/ItemReference

i am hoping to get

100 101

but all i get is

100

It seems the two Discount is stopping the node from being included
How do i fix my Predicate please?

This is in mysql so uses xpath 1.0

    select extractvalue( "<ProductSaleItem><ItemReference>100</ItemReference><Discount>                    <Modifier>1</Modifier></Discount></ProductSaleItem><ProductSaleItem>        <ItemReference>101</ItemReference><Discount><Modifier>1</Modifier></Discount>
<Discount><Modifier>2</Modifier></Discount></ProductSaleItem><ProductSaleItem>
<ItemReference>102</ItemReference</ProductSaleItem>","//ProductSaleItem[*/Modifier]/ItemReference") bob;

I have tried online xpath parsers and some use xpath 2.0 which seems to work but mysql refuses to work.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In the end I used

    //ProductSaleItem/Discount/Modifier/../../ItemReference


  2. 0

    According to Mysql docs extractvalue() returns the first node

    If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string.

    As suggested in the docs an iteration should be made (stored proc or similar)

    WHILE i < 4 DO
  SELECT xml, i, ExtractValue(xml, '//a[$i]');
  SET i = i+1;
END WHILE;

    May be getting the Modifiervalue could help as follows

    select extractvalue( "<ProductSaleItem>
  <ItemReference>100</ItemReference>
  <Discount><Modifier>1</Modifier></Discount>
</ProductSaleItem>
<ProductSaleItem>
  <ItemReference>101</ItemReference>
  <Discount><Modifier>1</Modifier></Discount>
  <Discount><Modifier>2</Modifier></Discount>
</ProductSaleItem>
<ProductSaleItem>
  <ItemReference>102</ItemReference>
</ProductSaleItem>","//ItemReference") bob,
extractvalue( "<ProductSaleItem><ItemReference>100</ItemReference><Discount>                    <Modifier>1</Modifier></Discount></ProductSaleItem><ProductSaleItem>        <ItemReference>101</ItemReference><Discount><Modifier>1</Modifier></Discount>
<Discount><Modifier>2</Modifier></Discount></ProductSaleItem><ProductSaleItem>
<ItemReference>102</ItemReference></ProductSaleItem>","//ProductSaleItem/Discount[1]/Modifier") alice;

    Output:

    +-------------+-------+
| bob         | alice |
+-------------+-------+
| 100 101 102 | 1 1   |
+-------------+-------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search