XML into CSV using SQL Server 2005 backend

Input string is in XML format Like

<Values>Karaikudi</Values>

<Values>Pallathur</Values>

<Values>Manachai</Values>


My output string should be Comma Separated Value like
Karaikudi,Pallathur,Manachai

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)' ),

@SubCategories=Categories.query('Categories/Sub')

FROM Sites where SiteID=@SiteID



Print @Main -- single String



-------------Cities





Print @Sub



<Values>Karaikudi</Values>

<Values>Pallathur</Values>

<Values>Manachai</Values>









--Table Variable to hold SubCategories



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



INSERT INTO @temp

SELECT

ParamValues.ID.value('.','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



--Karaikudi,Pallathur,Manachai

No comments: