Get specific XML Child node in SQL Server

Question

I am trying to load XMl to different tables with logical keys in MS SQL Server. I am stuck as I am getting all child nodes in SQL.

<Books>
<Book>
    <Name>Book1</Name>
    <Author>abc</Author>
    <Stores>
        <Name>Amazon</Name>
    </Stores>
</Book>
<Book>
    <Name>Book2</Name>
    <Author>cde</Author>
    <Stores>
        <Name>Flipkart</Name>
    </Stores>
</Book>
</Books>

I want to get result as below.

BookId  Name   Author  StoreXML
1       Book1  abc     <Stores><Name>Amazon</Name></Stores>  
2       Book2  cde     <Stores><Name>Flipkart</Name></Stores>

Gottfried Lesigang · Accepted Answer · 2016-12-14 11:37:55Z

4

Try it like this

DECLARE @xml XML=
N'<Books>
<Book>
    <Name>Book1</Name>
    <Author>abc</Author>
    <Stores>
        <Name>Amazon</Name>
    </Stores>
</Book>
<Book>
    <Name>Book2</Name>
    <Author>cde</Author>
    <Stores>
        <Name>Flipkart</Name>
    </Stores>
</Book>
</Books>';

SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS BookId --there is no id in your XML...
      ,b.value('(Name/text())[1]','nvarchar(max)') AS BookName 
      ,b.value('(Author/text())[1]','nvarchar(max)') AS Author
      ,b.query('Stores') AS StoreXML 
FROM @xml.nodes('/Books/Book') AS A(b)

answered Dec 14, 2016 at 11:37

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Pratik Patel Over a year ago

Thanks, I was using @xml.query rather than b.query to get specific child node.

Collectives™ on Stack Overflow

Get specific XML Child node in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related