How to get child node value in xml in sql server?

Maharudra Gadekar

4y2.3kSQL

Hi All,

I want to get child node value which is stored in database in xml format node is below like that.

dates
2022-03-01

so above is my sql server value in xml format and I want the value from FV node, but in xml no of FV node is present in XML. So please help me

thank in advance!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gajendra Jangid4y ago
1. DECLARE @xml XML=
2. N'
4. <Name>Book1Name>
5. abc
7. <Name>AmazonName>
11. <Name>Book2Name>
12. cde
14. <Name>FlipkartName>
17. ';
19. SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS BookId --there is no id in your XML...
20. ,b.value('(Name/text())[1]','nvarchar(max)') AS BookName
21. ,b.value('(Author/text())[1]','nvarchar(max)') AS Author
22. ,b.query('Stores') AS StoreXML
23. FROM @xml.nodes('/Books/Book') AS A(b)

Maharudra Gadekar4y ago

Hi Muhammad,

I understand your query and it's work also but one more dought can you please suggest me. how to return multiple records node in your query, because of i already tried your query and working as expected but in single record not more then one.

please suggest me.

Thanks in advace.

Muhammad Imran Ansari4y ago

You can get your output through following query:

DECLARE @xml XML=

dates

2022-03-01

SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Id --there is no id in your XML...

,b.value('(fn/text())[1]','nvarchar(max)') AS fn

,b.value('(fv/text())[1]','nvarchar(max)') AS fv

FROM @xml.nodes('/Main/field') AS A(b)

Output: