XML into CSV using SQL Server 2005 backend

Input string is in XML format Like




My output string should be Comma Separated Value like

Here is the code to do this operation.
1) Read the XML column from a Table
2) Put it into a Variable @Sub whose data type is in XML
3) Create a table variable @temp
4) @temp will hold all the SubItems in multiple rows.
5) COALESCE function is used to produce Comma separated values.

--Main Category and SubCategories

-- Extracting ElementValue from XML Column (Main)

---Extracting all the elements of a SubNode..(Sub)

declare @Main varchar(50) --- Only One Main Category

declare @Sub xml --- Multiple Sub Categories

SELECT @Main=Categories.value('(/Categories/Main/Values)[1] ', 'varchar(50)' ),


FROM Sites where SiteID=@SiteID

Print @Main -- single String


Print @Sub




--Table Variable to hold SubCategories

declare @temp table ([Sub] Varchar(50))




FROM @Sub.nodes('/Sub/Values') as ParamValues(ID)

-- Creating Comma Separated Values of All Sub Items..

declare @SubList varchar(max)

SELECT @SubList = COALESCE(cast(@SubList as varchar(max)) + ', ', '') +
cast([Sub] as VARCHAR(100))

FROM @temp

print @SubList


No comments: