sponsored links

Collapse

Announcement

Collapse
No announcement yet.

How to get node number in SQL/XML?

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

  • How to get node number in SQL/XML?

    I have following XML in /tmp/dta.xml *stfm file:

    Code:
    <group>
      <node>A</node>
      <data>
         <a>a1</a>
         <b>b1</b>
      </data>
      <data>
         <a>a2</a>
         <b>b2</b>
      </data>
    </group>
    <group>
      <node>A</node>
      <data>
         <a>a1</a>
         <b>b1</b>
      </data>
      <data>
         <a>a2</a>
         <b>b2</b>
      </data>
      <data>
         <a>a3</a>
         <b>b3</b>
      </data>
    
    </group>
    I need to know for each node belong pair a and b.

    I prepare somethig like this:
    Code:
    select xmldta.*
    from xmltable ('/group' passing
               xmlparse (document get_xml_file('/tmp/dta.txt') columns
                                   seq for ordinarlity,
                                   a char(5) path 'data/a',
                                   b char(5) path 'data/b'
                                  )
    I receive
    1 a1 b1
    2 a2 b2
    3 a1 b1
    4 a2 b2
    5 a3 b3

    but I need also "node" number. It means

    node
    1 1 a1 b1
    1 2 a2 b2
    2 3 a1 b1
    2 4 a2 b2
    2 5 a3 b3

    Element <node> can have the same value.
    Do you have any idea?

  • #2
    The use of
    Code:
    [fn:position]
    gives very promising results. I describe whole solution when I finish.

    Comment


    • #3
      Thanks for keep us in the loop!!!
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #4
        Does row_number help?

        SELECT
        XMLELEMENT(NAME "LineItem", xmlattributes(row_number() over (order by olprdc) as "lineItemNumber"),
        XMLFOREST(
        trim(olprdc) as "Product",
        oloqty as "Quantity",
        olsalp as "ExtendedPrice"
        )
        ) as lineItem
        FROM srbbspiql1
        where olcorn = '3102102'



        Results in:
        Code:
            <LineItem lineItemNumber="1">
                <Product>ANDM 026743N</Product>
                <Quantity>1.000</Quantity>
                <ExtendedPrice>6530.0000</ExtendedPrice>
            </LineItem>
            <LineItem lineItemNumber="2">
                <Product>ANDM 027955N</Product>
                <Quantity>1.000</Quantity>
                <ExtendedPrice>18480.0000</ExtendedPrice>
            </LineItem>
            <LineItem lineItemNumber="3">
                <Product>BAYM 026379N</Product>
                <Quantity>2.000</Quantity>
                <ExtendedPrice>5000.0000</ExtendedPrice>
            </LineItem>
            <LineItem lineItemNumber="4">
                <Product>BETM 026009N</Product>
                <Quantity>1.000</Quantity>
                <ExtendedPrice>11530.0000</ExtendedPrice>
            </LineItem>
        Your friends list is empty!

        Comment


        • #5
          You described the opposite situation (encapsulation) I need to do it during parsing. But as I wrote I have a solution but I will describe it when I finish the project.

          Comment

          Working...
          X