locked
Reading XML Elements along with Element ID RRS feed

  • Question

  • Hi, I have following example xml document.

        <Root>
            <A>
                <F>
                     <G>
    5</G>
                     < H > 6</H>
                </F>
            </A>
            <A>
                <F>
                     <G>
    5</G>
                     < H > 6</H>
                </F>
            </A>
        </Root>

    I need to read above XML in following manners.

    ----------------------
    ElementID      F     
    ----------------------
    1                     5
    1                     6
    2                     7
    2                     8

    Any Sugessions for such XML Parsing?
    I will be really thank for your sugessions.


    Regards,

    Zubair

    zubair
    Thursday, May 14, 2009 9:07 AM

Answers

  • You could use OPENXML for this:
    DECLARE @xml XML
    DECLARE @hDoc int
    
    SET @xml = '<Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
      </A>
      <A>
        <F>
          <G>7</G>
          <H>8</H>
        </F>
      </A>
    </Root>'
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    SELECT DENSE_RANK() OVER( ORDER BY id ) ElementID, F
    FROM OPENXML( @hDoc, '//F/*', 2 ) 
    WITH ( id int '@mp:parentid', F int '.' ) 
    
    EXEC sp_xml_removedocument @hDoc 
    
    • Proposed as answer by wBobEditor Thursday, May 14, 2009 10:00 AM
    • Marked as answer by Zubair Khalid Thursday, May 14, 2009 10:58 AM
    Thursday, May 14, 2009 9:59 AM
    Answerer
  • Couple of examples:
    DECLARE @xml XML
    SET @xml = '
    <Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
        <F>
          <G>7</G>
          <H>8</H>
        </F>
      </A>
      <A>
        <F>
          <X>9</X>
          <Y>10</Y>
        </F>
      </A>
    </Root>'
    
    -- Liang's CTE solution
    ;WITH cte AS
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY x) AS id,
            A.x.query('.') AS xmldoc
        FROM @xml.nodes('Root/A') AS A(x)
    )
    SELECT
        A.id,
        B.x.value('local-name(.)','varchar(10)') AS name,
        B.x.value('.','int') AS data
    FROM cte AS A
       CROSS APPLY A.xmldoc.nodes('/A/F/*') AS B(x)
       
    
    DECLARE @hDoc int
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    -- Multiple nested OPENXML calls
    SELECT DENSE_RANK() OVER( ORDER BY x.id ) AS ElementId, z.Col, z.Value
    FROM OPENXML( @hDoc, '//A', 2 )
    WITH ( id int '@mp:id' ) x
    	INNER JOIN OPENXML( @hDoc, '//A/*', 2 ) 
    WITH ( id int '@mp:id', parentid int '@mp:parentid' ) y
    	ON x.id = y.parentid
    	INNER JOIN OPENXML( @hDoc, '//A/*/*', 2 ) 
    WITH ( id int '@mp:id', parentid int '@mp:parentid', Col VARCHAR(2) '@mp:localname', Value int '.' ) z
    	ON y.id = z.parentid
    
    EXEC sp_xml_removedocument @hDoc
    GO
    As you can see, there's more than one way to do it.  The multiple calls to OPENXML feels a bit over-complicated so I think I prefer Liang's CTE solution.  However showing the Execution Plans shows in favour of the multiple OPENXML calls!?  I strongly recommend you test both solutions with your real data to see if they scale.
    • Marked as answer by Zubair Khalid Friday, May 15, 2009 12:55 PM
    Friday, May 15, 2009 12:10 PM
    Answerer
  • DECLARE
         @xml XML
    SET
     @xml = '<Root>
              <A>
                <F> 
                 <G>5</G>
                 <H>6</H>
                </F>
                <F> 
                 <G>7</G>
                 <H>8</H>
                </F>
              </A>
              <A>
                <F> 
                 <X>9</X>
                 <Y>10</Y>
                </F>
              </A>
            </Root>'
    ;
    
    WITH Liang AS
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY x) AS id,
            A.x.query('.') AS xmldoc
        FROM @xml.nodes('Root/A') AS A(x)
    )
    SELECT
        A.id,
        B.x.value('local-name(.)','varchar(10)') AS name,
        B.x.value('.','int') AS data
    FROM Liang AS A
       OUTER APPLY A.xmldoc.nodes('/A/F/*') AS B(x)
    

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by Zubair Khalid Friday, May 15, 2009 12:55 PM
    Friday, May 15, 2009 12:41 PM

All replies

  • You could use OPENXML for this:
    DECLARE @xml XML
    DECLARE @hDoc int
    
    SET @xml = '<Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
      </A>
      <A>
        <F>
          <G>7</G>
          <H>8</H>
        </F>
      </A>
    </Root>'
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    SELECT DENSE_RANK() OVER( ORDER BY id ) ElementID, F
    FROM OPENXML( @hDoc, '//F/*', 2 ) 
    WITH ( id int '@mp:parentid', F int '.' ) 
    
    EXEC sp_xml_removedocument @hDoc 
    
    • Proposed as answer by wBobEditor Thursday, May 14, 2009 10:00 AM
    • Marked as answer by Zubair Khalid Thursday, May 14, 2009 10:58 AM
    Thursday, May 14, 2009 9:59 AM
    Answerer
  • Thanks Alot,
    wBob


    you provided solution with same result.

    Again Thanks for sharing your solution.


    zubair
    Thursday, May 14, 2009 11:03 AM
  • Hi wBob,

    I have similar scenario as above but i need to different output.
    See the following example.

    DECLARE @xml XML

    SET @xml = '<Root>
    <A>
    <F>
    <G>5</G>
    <H>6</H>
    </F>
    </A>
    <A>
    <F>
    <X>7</X>
    <Y>8</Y>
    </F>
    </A>
    </Root>'
    Output Should be as follow
    ---------------------------------------
    ElementID        Col        Value
    ---------------------------------------
    1                        G            5
    1                        H            6
    2                        X            7
    2                        Y            8
    --------------------------------------

    Any Sugessions for such XML Parsing?
    I will be really thank for your sugessions.



    zubair
    Friday, May 15, 2009 5:12 AM
  • DECLARE @xml XML
    
    SET @xml = '<Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
      </A>
      <A>
        <F>
          <X>7</X>
          <Y>8</Y>
        </F>
      </A>
    </Root>';
    
    WITH Liang AS
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY x) AS id,
            A.x.query('.') AS xmldoc
        FROM @xml.nodes('Root/A/F') AS A(x)
    )
    SELECT
        A.id,
        B.x.value('local-name(.)','varchar(10)') AS name,
        B.x.value('.','int') AS data
    FROM Liang AS A
       OUTER APPLY A.xmldoc.nodes('/F/*') AS B(x)

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Proposed as answer by wBobEditor Friday, May 15, 2009 8:01 AM
    Friday, May 15, 2009 7:36 AM
  • Similar query:
    DECLARE @xml XML
    
    SET @xml = '<Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
      </A>
      <A>
        <F>
          <X>7</X>
          <Y>8</Y>
        </F>
      </A>
    </Root>'
    
    
    DECLARE @hDoc int
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    SELECT DENSE_RANK() OVER( ORDER BY id ) ElementID, Col, F
    FROM OPENXML( @hDoc, '//F/*', 2 ) 
    WITH ( id int '@mp:parentid', Col VARCHAR(2) '@mp:localname', F int '.' )
    
    EXEC sp_xml_removedocument @hDoc
    Great solution from Liang below as well.
    • Proposed as answer by wBobEditor Friday, May 15, 2009 8:01 AM
    Friday, May 15, 2009 8:01 AM
    Answerer
  • Actually i need to generate id for Element A (not F)
    I think following Example is more clear, what i need.

    DECLARE
    
     @xml XML
    
    
    SET
    
     @xml = '<Root>
    <A>
    <F>
    <G>5</G>
    <H>6</H>
    </F>
    <F>
    <G>7</G>
    <H>8</H>
    </F>
    </A>
    <A>
    <F>
    <X>9</X>
    <Y>10</Y>
    </F>
    </A>
    </Root>'

    And output should be as follow

    ---------------------------------------
    ElementID        Col        Value
    ---------------------------------------
    1                        G            5
    1                        H            6
    1                        G            7
    1                        H            8
    2                        X            9
    2                        Y            10
    --------------------------------------


    Thanks Alot, for both LiangCK and wBob
    for providing different suggestions.



    Zubair Khalid
    Friday, May 15, 2009 10:06 AM
  • Couple of examples:
    DECLARE @xml XML
    SET @xml = '
    <Root>
      <A>
        <F>
          <G>5</G>
          <H>6</H>
        </F>
        <F>
          <G>7</G>
          <H>8</H>
        </F>
      </A>
      <A>
        <F>
          <X>9</X>
          <Y>10</Y>
        </F>
      </A>
    </Root>'
    
    -- Liang's CTE solution
    ;WITH cte AS
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY x) AS id,
            A.x.query('.') AS xmldoc
        FROM @xml.nodes('Root/A') AS A(x)
    )
    SELECT
        A.id,
        B.x.value('local-name(.)','varchar(10)') AS name,
        B.x.value('.','int') AS data
    FROM cte AS A
       CROSS APPLY A.xmldoc.nodes('/A/F/*') AS B(x)
       
    
    DECLARE @hDoc int
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    -- Multiple nested OPENXML calls
    SELECT DENSE_RANK() OVER( ORDER BY x.id ) AS ElementId, z.Col, z.Value
    FROM OPENXML( @hDoc, '//A', 2 )
    WITH ( id int '@mp:id' ) x
    	INNER JOIN OPENXML( @hDoc, '//A/*', 2 ) 
    WITH ( id int '@mp:id', parentid int '@mp:parentid' ) y
    	ON x.id = y.parentid
    	INNER JOIN OPENXML( @hDoc, '//A/*/*', 2 ) 
    WITH ( id int '@mp:id', parentid int '@mp:parentid', Col VARCHAR(2) '@mp:localname', Value int '.' ) z
    	ON y.id = z.parentid
    
    EXEC sp_xml_removedocument @hDoc
    GO
    As you can see, there's more than one way to do it.  The multiple calls to OPENXML feels a bit over-complicated so I think I prefer Liang's CTE solution.  However showing the Execution Plans shows in favour of the multiple OPENXML calls!?  I strongly recommend you test both solutions with your real data to see if they scale.
    • Marked as answer by Zubair Khalid Friday, May 15, 2009 12:55 PM
    Friday, May 15, 2009 12:10 PM
    Answerer
  • DECLARE
         @xml XML
    SET
     @xml = '<Root>
              <A>
                <F> 
                 <G>5</G>
                 <H>6</H>
                </F>
                <F> 
                 <G>7</G>
                 <H>8</H>
                </F>
              </A>
              <A>
                <F> 
                 <X>9</X>
                 <Y>10</Y>
                </F>
              </A>
            </Root>'
    ;
    
    WITH Liang AS
    (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY x) AS id,
            A.x.query('.') AS xmldoc
        FROM @xml.nodes('Root/A') AS A(x)
    )
    SELECT
        A.id,
        B.x.value('local-name(.)','varchar(10)') AS name,
        B.x.value('.','int') AS data
    FROM Liang AS A
       OUTER APPLY A.xmldoc.nodes('/A/F/*') AS B(x)
    

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by Zubair Khalid Friday, May 15, 2009 12:55 PM
    Friday, May 15, 2009 12:41 PM
  • Thanks a lot both of you (LiangCK and wBob )
    for providing multiple solutions.


    Zubair Khalid
    Friday, May 15, 2009 1:01 PM