Archive
MSDN & Online Technical Articles on using XML with SQL
On this post I’m not going to discuss anything about XML. Here I’m listing out some links that I found very informative so that I can refer to them easily in future, and so can you.
My all previous posts on XML-SQL can be found at: https://sqlwithmanoj.wordpress.com/category/xml/
There are also lot of Technical Articles in MS BOL that discuss about using XML in SQL Server.
Here is a list of those:
What’s New in FOR XML: http://msdn.microsoft.com/en-US/library/ms345137(v=SQL.90).aspx
XML Best Practices: http://msdn.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx
XML Indexes: http://msdn.microsoft.com/en-US/library/ms345121(v=SQL.90).aspx
XML Options: http://msdn.microsoft.com/en-US/library/ms345110(v=SQL.90).aspx
XML Support: http://msdn.microsoft.com/en-US/library/ms345117(v=SQL.90).aspx
Performance Optimizations for the XML Data Type: http://msdn.microsoft.com/en-US/library/ms345118(v=SQL.90).aspx
… enjoy reading.
Please post your comments and links if you found any informative online article about XML-SQL.
Query XML string in tabular format – SQL Server
My previous post on XML discusses how to simply SELECT records from an XML string.
On this post I’m giving few more examples on how to SELECT records from an XML string. Here are some more examples to retrieve data in a relational tabular format by querying an XML string:
DECLARE @xml XML SET @xml=N'<Root> <stud id="1"> <fname>Manoj</fname> <lname>Pandey</lname> <class>10</class> <marks>80.5</marks> </stud> <stud id="2"> <fname>Saurabh</fname> <lname>Sharma</lname> <class>11</class> <marks>82.7</marks> </stud> <stud id="3"> <fname>Kanchan</fname> <lname>Pandey</lname> <class>10</class> <marks>90.5</marks> </stud> <stud id="4"> <fname>Rajesh</fname> <lname>Shah</lname> <class>11</class> <marks>70.3</marks> </stud> <stud id="5"> <fname>Kunal</fname> <lname>Joshi</lname> <class>12</class> <marks>64.7</marks> </stud> </Root>'; -- Select all records in tabular format from above XML string: SELECT Tab.Col.value('@id','int') AS ID, Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName, Tab.Col.value('lname[1]','nvarchar(20)') AS LastName, Tab.Col.value('class[1]','int') AS class, Tab.Col.value('marks[1]','float') AS Marks FROM @xml.nodes('/Root/stud') Tab(Col)
Output:- ID FirstName LastName Class Marks 1 Manoj Pandey 10 80.5 2 Saurabh Sharma 11 82.7 3 Kanchan Pandey 10 90.5 4 Rajesh Shah 11 70.3 5 Kunal Joshi 12 64.7
-- Select records as in Key-Value pair format: SELECT Tab.Col.value('../@id', 'int') AS RowID, Tab.Col.value('local-name(.)', 'nvarchar(max)') AS ColName, Tab.Col.value('text()[1]', 'nvarchar(max)') AS ColValue FROM @xml.nodes('/*/*/*') AS Tab(Col) GO
Output:- RowID ColName ColValue 1 fname Manoj 1 lname Pandey 1 class 10 1 marks 80.5 2 fname Saurabh 2 lname Sharma 2 class 11 2 marks 82.7 3 fname Kanchan 3 lname Pandey 3 class 10 3 marks 90.5 4 fname Rajesh 4 lname Shah 4 class 11 4 marks 70.3 5 fname Kunal 5 lname Joshi 5 class 12 5 marks 64.7