Wednesday, October 20, 2010

Read XML in SQL Server 2005/2008

Can read an xml file format in SQL Server 2005 2005/2008

To read the attributes use "@AttributeName"

To read the node under it use "NodeName[0]"

Just copy/patse the below given stuff in sql server and run it.


--//---------- Declare ----------//
DECLARE @xmlTest XML
SET @xmlTest = '<GUIDs>
<GUID Temp="a">1</GUID>
<GUID Temp="a">2</GUID>
<GUID Temp="a">3</GUID>
<GUID Temp="b">4</GUID>
<GUID Temp="b">5</GUID>
</GUIDs>'


--//---------- Set Query ----------//
SELECT Temp , MAX(GUID) FROM (
SELECT xmlTest.value('.', 'INT') AS GUID , xmlTest.value('@Temp', 'VARCHAR(36)') AS Temp
FROM @xmlTest.nodes('/GUIDs/GUID') d(xmlTest) ) AS A
GROUP BY Temp