Using XMLTABLE To Parse "Complex" XML

[TheZenbudda]

I have the following xml data:

<Things>
  <Thing ID="1">
    <Name>Thing1</Name>
    <Values>
      <Value Attribute="FieldName1">YADDA YADDA YADDA</Value>
      <Value Attribute="FieldName2">SO ON AND SO FORTH</Value>
      <ValueList Attribute="FieldName3"> 
        <Value>CA</Value>
        <Value>NY</Value>
        <Value>TX</Value>
      </ValueList>
      <ValueList Attribute="FieldName4">
        <Value>US</Value>
        <Value>CA</Value>
        <Value>GB</Value>
      </ValueList>
    </Values>
  </Thing>
  <Thing ID="2">
    <Name>Thing2</Name>
      <Values>
        <Value Attribute="FieldName1">YADDA YADDA YADDA</Value>
        <Value Attribute="FieldName2">SO ON AND SO FORTH</Value>
        <ValueList Attribute="FieldName3">
          <Value>CA</Value>
          <Value>NY</Value>
          <Value>TX</Value>
        </ValueList>
        <ValueList Attribute="FieldName4">
          <Value>US</Value>
          <Value>CA</Value>
          <Value>GB</Value>
          </ValueList>
        </Values>
      </Thing>
</Things>

I am trying to wrap my head around how to parse this xml to insert into db tables or into RPG data structures. I have determined that if I define the xpath expression as '$d/Things/Thing/Values/Value, then I can return a row for every instance of <Value Attribute>. However, I need the attribute value as well. Also, would I need to run a separate query for the multiple occurrences under ValueList?

[TheZenbudda]

I think I may have figured out. I found 1 example out there that had '../' as the path, but never explained what it was. So I tried various combinations and I think I got this to work:

select x.*
from mylib.myxmltable a, xmltable(
  '$xml/Things/Thing/Values/Value' passing a.xmlstring as "xml"
  columns
    thingId varchar(20) path '../../@ID'
    , name varchar(20) path '../../Name'
    , attribute varchar(64) path '@Attribute'
    , value varchar(64) path '.'
) as x

It seems if you need multiple rows to return from parsing xml, you need to declare the document root at the element name that begins the "multiple rows".

[B.Hauser]

Exactly, you always set up with the lowest level (i.e. the level with the multiple occurances) and then for the other information you move up (../ = 1 Level up)

You may have a look at the XPATH syntax:

https://www.w3schools.com/xml/xpath_syntax.asp