Retrive data from xml and get the format

Question

Hi I have data stored in table(cmsContentXml) in Column Name as xml

<Ingredients id="1515" parentID="1062" level="3" creatorID="1" sortOrder="0" createDate="2014-07-23T07:54:58" updateDate="2014-09-06T17:14:45" nodeName="Ingredients" urlName="ingredients" path="-1,1055,1062,1515" isDoc="" nodeType="1514" creatorName="Admin" writerName="ndopuch" writerID="2" template="0" nodeTypeAlias="Ingredients">
  <quickList>
     <![CDATA[1526,1530,1531,1536,1539,1549,1560,1564,1565,1567,1569,1572,1586,1587,1592,1594,1600,1601,1602,1651,1658,1660,1682,1696,1693,1715,1716,1717,1771,1799,1615,1669,1676,1685,1697,1730,1746,1757,1768,1779,1778,1785,1789,1794,1803,1805,1813,1826,1830,1843,1852,1858,1866,1880,1917,1913,1920,1927,1931,1936,1956,1959,1872]]>
  </quickList>
</Ingredients>

and I want to retrieve data as below "-1,1055,1062,1515" getting from path section and 1526,1530,1531 is coming from quickList of xml node

-1,1055,1062,1515,1526
-1,1055,1062,1515,1530
-1,1055,1062,1515,1531
-1,1055,1062,1515,1536
.
.
.
-1,1055,1062,1515,1872

I have tried retrieving data as

SELECT
[xml].value('(/Ingredients/quickList)', 'nvarchar(max)') as data
FROM [cmsContentXml]  where [nodeId]=1515

but giving error as

Cannot find either column "xml" or the user-defined function or aggregate "xml.value", or the name is ambiguous.

Sample data format

CREATE TABLE #cmsContentXml(
    nodeid [int],
    [xml] [nvarchar](max) NOT NULL    
)

INSERT INTO #cmsContentXml VALUES (1515,'<Ingredients id="1515" parentID="1062" level="3" creatorID="1" sortOrder="0" createDate="2014-07-23T07:54:58" updateDate="2014-09-06T17:14:45" nodeName="Ingredients" urlName="ingredients" path="-1,1055,1062,1515" isDoc="" nodeType="1514" creatorName="Admin" writerName="ndopuch" writerID="2" template="0" nodeTypeAlias="Ingredients">
    <quickList><![CDATA[1526,1530,1531,1536,1539,1549,1560,1564,1565,1567,1569,1572,1586,1587,1592,1594,1600,1601,1602,1651,1658,1660,1682,1696,1693,1715,1716,1717,1771,1799,1615,1669,1676,1685,1697,1730,1746,1757,1768,1779,1778,1785,1789,1794,1803,1805,1813,1826,1830,1843,1852,1858,1866,1880,1917,1913,1920,1927,1931,1936,1956,1959,1872]]></quickList>
</Ingredients>
');

Please help me out to retrieve data as

 Data_Column
-1,1055,1062,1515,1526
-1,1055,1062,1515,1530
-1,1055,1062,1515,1531
-1,1055,1062,1515,1536
.
.
.
-1,1055,1062,1515,1872

[Here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great place to start. — Sean Lange, Sep 19 '18 at 14:47

score 0 · Accepted Answer · answered Sep 19 '18 at 15:13

This should work:

DECLARE @xml xml ='<Ingredients id="1515" parentID="1062" level="3" creatorID="1" sortOrder="0" createDate="2014-07-23T07:54:58" updateDate="2014-09-06T17:14:45" nodeName="Ingredients" urlName="ingredients" path="-1,1055,1062,1515" isDoc="" nodeType="1514" creatorName="Admin" writerName="ndopuch" writerID="2" template="0" nodeTypeAlias="Ingredients">
  <quickList>
     <![CDATA[1526,1530,1531,1536,1539,1549,1560,1564,1565,1567,1569,1572,1586,1587,1592,1594,1600,1601,1602,1651,1658,1660,1682,1696,1693,1715,1716,1717,1771,1799,1615,1669,1676,1685,1697,1730,1746,1757,1768,1779,1778,1785,1789,1794,1803,1805,1813,1826,1830,1843,1852,1858,1866,1880,1917,1913,1920,1927,1931,1936,1956,1959,1872]]>
  </quickList>
</Ingredients>'

SELECT Path+','+Num FROM (
    SELECT Path.value('.', 'nvarchar(MAX)')
    FROM @xml.nodes('Ingredients/@path') X(Path)
) T1(Path)
CROSS JOIN (
    SELECT REPLACE(REPLACE(A.value('.','nvarchar(MAX)'),' ',''),char(10),'') FROM
    (
        SELECT CAST('<a>'+REPLACE(quickList.value('.','nvarchar(MAX)'),',','</a><a>')+'</a>' as xml) X
        FROM @xml.nodes('Ingredients/quickList') X(quickList)
    ) qlXml
    CROSS APPLY X.nodes('a') T(A)
) T2(Num)

Stephane · Answer 2 · 2018-09-19T16:03:07.200

The first problem that you face is the fact that you handle an nvarchar like an xml. You must cast it as an xml is you want to do anything.

The other problem you have is you query: you must mention sql server that you get the inner content of the node with text()

The following problem is the fact you must ask to take the first element of the xml using [1] otherwise sql server won't be able to know how you want to handle the possible list it could get.

Then you will need to split the string you get from the xml. Since sql server 2016, you have the STRING_SPLIT function. Finally, a cross apply to distribute all the splitted values on you row.

with parsedAsXml as (
    select 
        c.nodeId as NodeId, 
        cast(c.[xml] as xml) as [xml]
    FROM 
        #cmsContentXml as c
),
sub as (
    SELECT 
        c.nodeId as NodeId, 
        c.[xml].value('(/Ingredients/quickList/text())[1]', 'nvarchar(max)') as QuickList,
        c.[xml].value('(/Ingredients/@path)[1]', 'nvarchar(max)') as path
    FROM 
        parsedAsXml as c
)
SELECT 
    s.[path] + ',' + ql.[Value]
FROM 
    sub as s
    cross apply STRING_SPLIT(s.QuickList, ',') as ql
where 
    s.NodeId=1515;

If you are on sql server 2008, you must make your own split function (called here splitstring, shamely copied from T-SQL split string).

CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX), @separator VARCHAR(50) )
RETURNS
 @returnList TABLE ([Value] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(@separator, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@separator, @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
GO

Retrive data from xml and get the format

2 Answers2