Click here to Skip to main content
13,737,226 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a XML column in SQL Server Database table.

i Want to get Result from that XML Column.

How can i do this Below is my Table Column

<string xmlns="http://www.webserviceX.NET">
      <Country>Sri Lanka</Country>
      <Country>Sri Lanka</Country>
      <Country>Sri Lanka</Country>
      <Country>Sri Lanka</Country>
      <Country>Sri Lanka</Country>

What I have tried:

    [Xml_Data].value('(string/NewDataSet/Table/Country)[1]', 'nvarchar(max)') as FirstName
    ,[Xml_Data].value('(string/NewDataSet/Table/City)[1]', 'nvarchar(max)') as LastName
Posted 8-Nov-17 18:18pm
Updated 8-Nov-17 19:19pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

It's because of the XMLNS.
Try following query and should work perfectly.
;WITH XMLNAMESPACES (N'http://www.webserviceX.NET' as X)
    [Xml_Data].value('(/X:string/X:NewDataSet/X:Table/X:Country)[1]', 'nvarchar(max)') as FirstName
    ,[Xml_Data].value('(/X:string/X:NewDataSet/X:Table/X:City)[1]', 'nvarchar(max)') as LastName

Your old query will still work if you just remove the xmlns namespace.

Further reading : Add Namespaces to Queries with WITH XMLNAMESPACES | Microsoft Docs[^]

Hope, it helps :)
Udara Eshan Ariyarathne 9-Nov-17 1:23am
Thanks a Lot Suvendu, you save my day !!!
Suvendu Shekhar Giri 9-Nov-17 1:36am
Glad to know that it helped!
Thanks :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 9 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100