Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 8-Nov-17 19:19pm

1 solution

It's because of the XMLNS.
Try following query and should work perfectly.
SQL
;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 :)
 
Share this answer
 
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900