ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using XMLTABLE To Parse "Complex" XML

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using XMLTABLE To Parse "Complex" XML

    I have the following xml data:


    Code:
    <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?

  • #2
    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:

    Code:
    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".




    Comment


    • #3
      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:
      W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

      Comment

      Working...
      X