Click here to Skip to main content
13,259,539 members (55,541 online)
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">
  <NewDataSet>
    <Table>
      <Country>Sri Lanka</Country>
      <City>Katunayake</City>
    </Table>
    <Table>
      <Country>Sri Lanka</Country>
      <City>Anuradhapura</City>
    </Table>
    <Table>
      <Country>Sri Lanka</Country>
      <City>Batticaloa</City>
    </Table>
    <Table>
      <Country>Sri Lanka</Country>
      <City>Ratmalana</City>
    </Table>
    <Table>
      <Country>Sri Lanka</Country>
      <City>Trincomalee</City>
    </Table>
  </NewDataSet>
</string>


What I have tried:

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

1 solution

Rate this: bad
 
good
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)
SELECT
    [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
FROM [AP_DEMO]


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 :)
  Permalink  
Comments
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 |
Web01 | 2.8.171114.1 | Last Updated 9 Nov 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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