Read Xml format

« SQL SERVER – Simple Example of Creating XML File Using T-SQL
SQL SERVER – Restore Master Database – An Easy Solution »
SQL SERVER – Simple Example of Reading XML File Using T-SQL
February 13, 2009 by pinaldave
In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.
Following is the XML which we will read using T-SQL:
Following is the T-SQL script which we will be used to read the XML:
DECLARE @MyXML XMLSET @MyXML = 'WhiteBlueBlackGreenRedApplePineappleGrapesMelon'
SELECTa.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’++a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4FROM @MyXML.nodes(‘SampleXML’) a(b)