Rows to Comma Separated Values Using Xml Tag

Rows to comma separated values using XML tag

SAMPLE TABLES

SELECT * INTO Categories
FROM
(
   SELECT 1148 CategoryId, 581771 PostId
   UNION ALL
   SELECT 1183 CategoryId, 581771 PostId
   UNION ALL
   SELECT 1184 CategoryId, 581771 PostId   
)TAB

SELECT * INTO TagContent
FROM
(
SELECT 1 [Id], '<blockquote><p><a href="abc.com"></p></blockquote>' TagContent ,    '2014-11-08' StartDate, '2014-11-14' EndDate,  1148 CategoryID, NULL TagTitle     

UNION ALL   
SELECT  2, '<blockquote><p><a href="abc.com"></p></blockquote>',     '2014-11-25', '2014-12-05',  1183,     '<h1>Aging Title</h1>' 

UNION ALL
SELECT  3, '<blockquote><p><a href="abc.com"></p></blockquote>',     '2014-11-25', '2014-11-27',  1184,     '<h1>Allergies Title</h1>' 
)TAB

Now we convert TagTitle to Ambersand seperated valuesfor the same TagContent. Since XML format is used we need to replace >, < and&to <, > and &.

QUERY

SELECT DISTINCT TagContent,STUFF(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(
        (SELECT '&' + TagTitle
        FROM TagContent T2 
        WHERE ST.TagContent=T2.TagContent 
        FOR XML PATH('')),2,200000),'<','<'),'>','>'),'&','&'),'amp;',''),1,'') TagTitle
        FROM Categories CPC
        JOIN TagContent ST ON CPC.CategoryId=ST.CategoryId
  • SQL FIDDLE

Convert List Of XML Tags in varchar column to comma separated list

Without proper sample data it's hard to give an exact query. But you would do something like this

  • Use CROSS APPLY to convert the varchar to xml
  • Use .nodes to shred the XML into separate rows.
  • Join using .value to get the id attribute
  • Group up, and concatenate using STRING_AGG. You may not need GROUP BY depending on your situation.
SELECT
  xt.Id,
  STRING_AGG(ot.Value, ',')
FROM XmlTable xt
CROSS APPLY (SELECT CAST(xt.XmlColumn AS xml) ) v(XmlData)
CROSS APPLY v.XmlData.nodes('/choice') x1(choice)
JOIN OtherTable ot ON ot.Id = x1.choice.value('@id','int')
GROUP BY
  xt.Id;

I would advise you to store XML data in an xml typed column if at all possible.

get comma separated values from xml data

Please try the below SQL query

DECLARE @commaSeparatedValues NVARCHAR(MAX)
DECLARE @xml XML = N'
<e>
  <id>1</id>
  <name>test1</name>
  <istest>1</istest>
</e>
<e>
  <id>2</id>
  <name>test2</name>
  <istest>2</istest>
</e>
'

;with cte as (
    select 
        rownr = ROW_NUMBER() over (order by @commaSeparatedValues),
        Tbl.col.query('.') as [xml]
    from @xml.nodes('e') Tbl(col)
), cols as (
    select
        rownr,
        Tbl.Col.value('.', 'nvarchar(max)') as Value
    from cte
    cross apply cte.xml.nodes('//text()') Tbl(Col)
)
select distinct 
     STUFF((
       SELECT ',' + IIF(ISNUMERIC(value) = 1, Value, '''' + Value + '''')
       FROM cols SSF WHERE SSF.rownr = S.rownr
       FOR XML PATH(''),TYPE
       ).value('.','VARCHAR(MAX)'
     ), 1, 1, '')
    from cols S

I use SQL row_number() function to number records and distinguish column values when they are separated into values (the second CTE uses Partition By clause to sort columns among row data)

Then I concatenate string values into comma separated string using SQL string concatenation method with using XML PATH()

I hope it helps

Get comma separated values from an xml in SQL

This is a fully working example.

You told us, that performance matters, so do not use scalar UDF!

Try it like this (next time it's your job to create a (reduced!!!) MCVE:

CREATE DATABASE testDB;
GO
USE testDB;
GO
CREATE TABLE Booking(BookingID INT CONSTRAINT PK_Booking PRIMARY KEY
                    ,SomeBookingData VARCHAR(100));
INSERT INTO Booking VALUES(1,'Booking 1'),(2,'Booking 2');

CREATE TABLE BookingInfo(BookingID INT CONSTRAINT FK_BookingInfo_BookingID FOREIGN KEY REFERENCES Booking(BookingID)
                        ,SomeOtherInfo VARCHAR(100)
                        ,FareDetails XML);
INSERT INTO BookingInfo VALUES
 (1,'First row for ID=1, returns AP,AP'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>')
,(1,'Second row for ID=1, returns XY,MN'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>XY</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>MN</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>')
,(2,'row with ID=2, returns AA,BB'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AA</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>BB</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>');
GO

--This is the function. It returns as table and is fully inlined (no BEGIN...END!)

CREATE FUNCTION dbo.CreateBookingInfoCSV(@BookingID INT)
RETURNS TABLE
AS
RETURN
SELECT STUFF(
(
    SELECT ','+REPLACE(FareDetails.query(N'data(/AirFareInfo/PTSDPFS/PTSD/FBC)').value(N'.',N'nvarchar(max)'),' ',',')
    FROM BookingInfo AS bi
    WHERE bi.BookingID=@BookingID
    FOR XML PATH('')
),1,1,'') AS BookingInfoCSV;
GO

--Hint the trick with XQuery data() function will break, if your values contain blanks!

--The following SELECT calls all rows from Booking-table and gets the fitting details

SELECT b.BookingID
      ,b.SomeBookingData
      ,A.BookingInfoCSV 
FROM Booking AS b
OUTER APPLY dbo.CreateBookingInfoCSV(b.BookingID) AS A;
GO

--Clean up (carefull with real data!)

USE master;
GO
DROP DATABASE testDB;

--The result

BookingID   SomeBookingData BookingInfoCSV
1           Booking 1       AP,AP,XY,MN
2           Booking 2       AA,BB

How to split comma separated values stored in XML node and display as individual records Without using function- SQL Server 2012

Perhaps this is a little cleaner than the prior post

Example

Select A.ID
      ,C.*
 From  Chart A
 Cross Apply (
                Select Item   = x.v.value('@NAME','VARCHAR(max)')
                      ,Value  = x.v.value('.[1]','VARCHAR(max)')
                 From  XMLvalue.nodes('/BETA/*') x(v)
             ) B
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(B.Value,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) C

Returns

ID  RetSeq  RetVal
1   1       ASSESSMENT=1
1   2       ASSESSMENT=2
1   3       ASSESSMENT=3
2   1       ASSESSMENT=4
2   2       ASSESSMENT=5
2   3       ASSESSMENT=6
3   1       ASSESSMENT=7
3   2       ASSESSMENT=8
3   3       ASSESSMENT=9
1   1       PROJECT=1
1   2       PROJECT=2
1   3       PROJECT=3
2   1       PROJECT=4
2   2       PROJECT=5
2   3       PROJECT=6
3   1       PROJECT=7
3   2       PROJECT=8
3   3       PROJECT=9

EDIT -

Use OUTER APPLY if you want to see NULL values.

Converting XML node values to comma separated values in SQL

Try this;

DECLARE @dataCodes XML = '<Root>
                    <List Value="120" />
                    <List Value="110" />
                </Root>';      

DECLARE @ConcatString VARCHAR(MAX)

SELECT @ConcatString = COALESCE(@ConcatString + ', ', '') + Code
FROM (
    SELECT T.Item.value('@Value[1]', 'VARCHAR(MAX)') AS Code
    FROM @dataCodes.nodes('/Root/List') AS T(Item)
    ) as TBL

SELECT @ConcatString AS Result
GO

You just need to add an alias to your sub SQL query.

Convert multiple rows into one with comma as separator

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

Get XML nodes from SQL Server column as comma-separated list

Try this:

SELECT
    STUFF((SELECT 
              ',' + fd.v.value('(.)[1]', 'varchar(10)')
           FROM 
              Temp12345
           CROSS APPLY
              col1.nodes('/fd/field/v') AS fd(v)
           FOR XML PATH('')
          ), 1, 1, '')

This gives me A,B,C - does it work for you, too?

How to make XML values comma separated using XPath, XQuery in SQL Server

SQL Server does not implement the xPath function string-join, so you would need to adopt a two step process, the first would be to extract all the terms to rows using nodes();

SELECT  n.value('.', 'VARCHAR(100)') AS parsedString
FROM    #temp AS t
        CROSS APPLY t.Response.nodes('/error/description2') r (n);

Which gives you your values as rows:

parsedString
----------------------------------------------------------------------------
Country Code is required
Error While Saving the Project info
Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project

Then you can add your delimeter and concatenate them back up, using FOR XML PATH(''), TYPE, and finally use STUFF to remove the first delimeter:

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    #temp AS t
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

FULL WORKING EXAMPLE

DECLARE @X XML = '<error>
  <errorno>BL04002055</errorno>
  <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description>
  <description2>Country Code is required</description2>
  <correction />
</error>
<error>
  <errorno>BL01001973</errorno>
  <description />
  <description2>Error While Saving the Project info</description2>
  <correction />
</error>
<error>
  <errorno>Unable to Create Custom Object</errorno>
  <description />
  <description2>Smart Rule PROJECT_COUNTRYCODE_VAL Violated: Country Code is required
Error While Saving the Project info</description2>
  <correction />
</error>';

SELECT  STUFF(( SELECT  ',' + n.value('.', 'VARCHAR(100)') AS parsedString
                FROM    (SELECT @X) AS t (Response)
                        CROSS APPLY t.Response.nodes('/error/description2') r (n)
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ParsedString;

Get comma separated string into each row of CSV using XSLT

One way you could handle this is to adapt the solution I linked to in the comments as follows:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:param name="items">item1,item2,item3,item4</xsl:param>

<xsl:template match="/PersonList">
    <!-- header -->
    <xsl:text>Id,Name,Adress,ItemValue
</xsl:text>
    <!-- data -->
    <xsl:for-each select="Person">
        <xsl:value-of select="ID"/>
        <xsl:text>,</xsl:text>  
        <xsl:value-of select="Name"/>
        <xsl:text>,</xsl:text>  
        <xsl:value-of select="Address"/>
        <xsl:text>,</xsl:text>  
        <xsl:call-template name="get-Nth-value">
            <xsl:with-param name="list" select="$items"/>
            <xsl:with-param name="N" select="position()"/>
        </xsl:call-template>
        <xsl:text>
</xsl:text>  
        <!-- recursive call -->
    </xsl:for-each>
</xsl:template>

<xsl:template name="get-Nth-value">
    <xsl:param name="list"/>
    <xsl:param name="N"/>
    <xsl:param name="delimiter" select="','"/>
    <xsl:choose>
        <xsl:when test="$N = 1">
            <xsl:value-of select="substring-before(concat($list, $delimiter), $delimiter)"/>
        </xsl:when>
        <xsl:when test="contains($list, $delimiter) and $N > 1">
            <!-- recursive call -->
            <xsl:call-template name="get-Nth-value">
                <xsl:with-param name="list" select="substring-after($list, $delimiter)"/>
                <xsl:with-param name="N" select="$N - 1"/>
                <xsl:with-param name="delimiter" select="$delimiter"/>
            </xsl:call-template>
        </xsl:when>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

When applied to your example input (corrected for well-formedness!), this will return:

Result

Id,Name,Adress,ItemValue
1,Name1,Add1,item1
2,Name2,Add2,item2
3,Name3,Add3,item3
4,Name4,Add4,item4

However, it might be better to reduce the number of required iterations by processing both the XML and the $items parameter in parallel:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:param name="items">item1,item2,item3,item4</xsl:param>

<xsl:template match="/PersonList">
    <!-- header -->
    <xsl:text>Id,Name,Adress,ItemValue
</xsl:text>
    <!-- data -->
    <xsl:call-template name="generate-rows">
        <xsl:with-param name="persons" select="Person"/>
        <xsl:with-param name="items" select="$items"/>
    </xsl:call-template>
</xsl:template>

<xsl:template name="generate-rows">
    <xsl:param name="persons"/>
    <xsl:param name="items"/>
    <xsl:param name="delimiter">,</xsl:param>
    <xsl:if test="$persons">
        <xsl:variable name="person" select="$persons[1]" />
        <!-- write to output -->
        <xsl:value-of select="$person/ID"/>
        <xsl:text>,</xsl:text>  
        <xsl:value-of select="$person/Name"/>
        <xsl:text>,</xsl:text>  
        <xsl:value-of select="$person/Address"/>
        <xsl:text>,</xsl:text>  
        <xsl:value-of select="substring-before(concat($items, $delimiter), $delimiter)"/>
        <xsl:text>
</xsl:text>  
        <!-- recursive call -->
        <xsl:call-template name="generate-rows">
            <xsl:with-param name="persons" select="$persons[position() > 1]"/>
            <xsl:with-param name="items" select="substring-after($items, $delimiter)"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>

Note that this assumes that the $items parameter value is supplied at runtime. If it can be hard-coded, then the solution could be much simpler.

And we still don't know what should happen when the number of items is not equal to the number of persons.


Related Topics

Query the Two Cities in Station with the Shortest and Longest City Names,

How to Create a One-Time-Use Function in a Script or Stored Procedure

How to Select Using with Recursive Clause

Sql: When It Comes to Not in and Not Equal To, Which Is More Efficient and Why

Managing Hierarchies in SQL: Mptt/Nested Sets VS Adjacency Lists VS Storing Paths

How to Add a Column to Large SQL Server Table

Postgresql Get a Random Datetime/Timestamp Between Two Datetime/Timestamp

What Is Wrong with a Transitive Dependency

Oracle Insert via Select from Multiple Tables Where One Table May Not Have a Row

How to Do a Find/Replace in T-Sql

SQL Full Text Search VS "Like"

Insert Default Value When Parameter Is Null

How to Group Nearby Latitude and Longitude Locations Stored in SQL

Delete the 'First' Record from a Table in SQL Server, Without a Where Condition

Is Substr or Like Faster in Oracle

Ms Access Query to SQL Server - Distinctrow

Hierarchical Tagging in SQL

How to Convert SQL Unpivot Query to Hana SQL


Leave a reply



Submit