Monday, February 27, 2012

SQL Server : Sample for reading xml data

Goal

Simple SQL query to fetch data from an xml.
--//---------- Declare ----------//
DECLARE @xmlSample XML;

SET @xmlSample = '<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 * FROM 
(
    SELECT 
        d.value('.', 'INT') AS GUID , 
        d.value('@Temp', 'VARCHAR(36)') AS Temp
    FROM @xmlSample.nodes('/GUIDs/GUID') d(d) 
) AS A


No comments:

Post a Comment