Tuesday, April 21, 2009

How to select XML data as SQL table in SQl server 2005

How to select XML data as SQL table in SQl server 2005.

we can query xml datatype in sql server to retrive all nodes as SQL table.
xml datatype is new in SQL Server 2005.

if we have XML data like :

declare @UserXML XML

set @UserXML='<Users><User><ID>1</ID><Name>Haresh,Herry</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>2</ID><Name>Paresh,Pariyo,Perry</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>3</ID><Name>Ajay,Sinh,Bapu</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>4</ID><Name>Abhijit</Name></User></Users>'


We can query them as :

SELECT tableName.columnName.value('ID[1]','int')AS ID, tableName.columnName.value('Name[1]','VARCHAR(100)')AS [Name], tableName.columnName.value('IsLoggedIn[1]','bit')AS IsLoggedIn
FROM @UserXML.nodes('//User') tableName(columnName)


after executing above Query output will be:


Figure-1(Output)







Thanks.



No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author