Querying XML file in SQL Server

Question

This is my xml file

<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Parents>
    <Parent id="1234">
      <name>
        <firstname>ABC</firstname>
        <lastname>XYSX</lastname>
      </name>
    </Parent>
    <Parent id="1235">
      <name>
        <firstname>TFU</firstname>
        <lastname>GHY</lastname>
      </name>
    </Parent>
  </Parents>
  <Children>
    <Child id="457" Parentid="1234">
      <name>
        <cfirstname>JOHN</cfirstname>
        <clastname>SMITH</clastname>
      </name>
    </Child>
    <Child id="459" Parentid="1235">
      <name>
        <cfirstname>DAVID</cfirstname>
        <clastname>SMITH</clastname>
      </name>
    </Child>
  </Children>
</Detials>

I stored it in a table (using Bulk Insert).

When I query like this

SELECT
    x.c.value('(./Parents/Parent/@id)[1]', 'nvarchar(4)' ) AS id
FROM 
    T1 s
CROSS APPLY 
    s.XMLData.nodes('Detials') AS x(c)

I get the result as

Id
----
1234

When I changed it a little bit

SELECT
    x.c.value('@id', 'nvarchar(4)' ) AS id
FROM 
    T1 s
CROSS APPLY 
    s.XMLData.nodes('/Detials/Parents/Parent') AS x(c)

I get:

Id
----
1234
1235

I just want to query them to result like this

Parent_id | firstname | lastname | Child_id | Child_First_name | child_last_name 
----------+-----------+----------+----------+------------------+-----------
1234      | ABC       | XYSX     |    457   |      JOHN        | SMITH 
1235      | TFU       | GHY      |    459   |      DAVID       | SMITH

How could I do this in query ?

In my result I want total no. of rows which depends on the Parentid. If my xml file contains 6 <parent id> then my query should show all 6 rows for each parent id's along with comparing the Childid's to also be included if the Parentid matches in <child> tag.

Thanks, Jayendran

John Cappelletti · Accepted Answer · 2017-06-06 13:56:07Z

1

You were on the right track, but this would require a left join on the two result sets Parent/Children

Example

Declare @T1 table (ID int,XMLData xml) 
Insert Into @T1 values
(1,'<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Parents><Parent id="1234"><name><firstname>ABC</firstname><lastname>XYSX</lastname></name></Parent><Parent id="1235"><name><firstname>TFU</firstname><lastname>GHY</lastname></name></Parent></Parents><Children><Child id="457" Parentid="1234"><name><cfirstname>JOHN</cfirstname><clastname>SMITH</clastname></name></Child><Child id="459" Parentid="1235"><name><cfirstname>DAVID</cfirstname><clastname>SMITH</clastname></name></Child></Children></Detials>')

Select A.ID
      ,B.*
 From  @T1 A
 Cross Apply (
                Select B1.Parent_id
                      ,B1.First_Name
                      ,B1.Last_Name
                      ,B2.Child_id  
                      ,B2.Child_First_name 
                      ,B2.child_last_name  
                 From  (
                        Select
                            Parent_id  = x.c.value('@id', 'nvarchar(4)' )
                           ,First_Name = x.c.value('(name/firstname)[1]','varchar(50)')
                           ,Last_Name  = x.c.value('(name/lastname)[1]','varchar(50)')
                        From @T1 s
                        Cross Apply s.XMLData.nodes('/Detials/Parents/Parent') AS x(c)
                       ) B1
                 Left Join  (
                        Select 
                            pt_id      = x.c.value('@Parentid', 'nvarchar(4)' )
                           ,Child_id   = x.c.value('@id', 'nvarchar(4)' )
                           ,Child_First_name  = x.c.value('(name/cfirstname)[1]','varchar(50)')
                           ,child_last_name   = x.c.value('(name/clastname)[1]','varchar(50)')
                        From @T1 s
                        Cross Apply s.XMLData.nodes('/Detials/Children/Child ') AS x(c)
                       ) B2
                   on  B1.Parent_id = B2.pt_id
             ) B

Returns

answered Jun 6, 2017 at 13:56

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jayendran Over a year ago

Thanks John,I've have some doubts here .Let as assume that my xml file contains additionally <Child id="555" Parentid="1234"> <Child id="655" Parentid="1234"> Under <Children>. Then,the final result will be like this 1|1234|ABC|XYSX|457.. 1|1234|ABC|XYSX|555.. 1|1234|ABC|XYSX|555.. 1|1235|TFU|GHY|459.. (adding from your Result Screen shot) Is this Correct ?

John Cappelletti Over a year ago

@JayendranRosh Then Parent_ID 1234 would have a total of 3 records. The original plus the two you just added.

John Cappelletti Over a year ago

@JayendranRosh See dbfiddle.uk/…

Gottfried Lesigang · Accepted Answer · 2017-06-06 20:38:59Z

0

I'd solve this in one single go:

SELECT Parent.ID
      ,p.value('(name/firstname/text())[1]','nvarchar(max)')
      ,p.value('(name/lastname/text())[1]','nvarchar(max)')
      ,c.value('@id','int')
      ,c.value('(name/cfirstname/text())[1]','nvarchar(max)')
      ,c.value('(name/clastname/text())[1]','nvarchar(max)')
FROM @xml.nodes('/Detials/Parents/Parent') AS A(p)
OUTER APPLY (SELECT p.value('@id','int')) AS Parent(ID)
OUTER APPLY @xml.nodes('/Detials/Children/Child[@Parentid=sql:column("Parent.ID")]') AS B(c);

The trick is, to read the Parentid into a named column via APPLY. This value can be used as predicate to fetch the children via sql:column().

answered Jun 6, 2017 at 20:38

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

Querying XML file in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related