Thursday, 27 March 2014

How to select node values from XML in SQL Server

CREATE FUNCTION Fn_GetSelectedFilters (  
      @XmlData NVARCHAR(MAX)
)
RETURNS @FilterData TABLE (
      Filter INT,
      FilterType NVARCHAR(200)
      )
AS
BEGIN
      DECLARE @XML AS XML;

      SET @XML = CAST(@XmlData AS XML)
                 
      ------------------- Start : Regions -----------------
      INSERT INTO @FilterData
      SELECT xmlData.Col.value('.', 'varchar(max)'),
            'Regions'
      FROM @XML.nodes('//Regions/int') xmlData(Col);       
      ------------------- End : Regions -------------------

     
      ------------------- Start : Countries ---------------
      INSERT INTO @FilterData
      SELECT xmlData.Col.value('.', 'varchar(max)'),
            'Countries'
      FROM @XML.nodes('//Countries/int') xmlData(Col);     
      ------------------- End : Countries -----------------            
     
RETURN
END


GO

SELECT * FROM dbo.Fn_GetSelectedFilters('<SelectedFilters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Regions>
    <int>1</int>
    <int>2</int>
  </Regions>
  <Countries>
    <int>101</int>
    <int>102</int>
    <int>301</int>
  </Countries> 
</SelectedFilters>')


Output:

Filter      FilterType
----------- -------------------
1           Regions
2           Regions
101         Countries
102         Countries
301         Countries

(5 row(s) affected)

No comments:

Post a Comment