getting xml node as comma delimited list via select statement

Question

Xml is stored in a varchar column.

One of the nodes is like

<PreviousItem>
<string>501</string>
<string>502</string>
<string>505</string>
</PreviousItem>

I need to get this nodes elements as comma delimited list (having issue with this part only) along with other items in this xml and the table.

SELECT
     nId, cDescription, 
    ,CAST(cSettings AS XML).value('data(/clsSettings/PreviousItem)[1]','nvarchar(max)') AS PreviousItem
    ,CAST(cSettings AS XML).value('data(/clsSettings/RegistrationType)[1]','nvarchar(50)') AS RegistrationType
FROM tblX

The above is giving me PreviousItem as 501502505.

i have tried following but i am getting Incorrect syntax near the keyword 'AS' in CAST(cSettings AS XML)

( SELECT    
            STUFF(( SELECT  ',' + Prods.Prod.value('text()[1]','varchar(max)')
                    FROM    CAST(cSettings AS XML).nodes('/clsSettings/PreviousItem') AS Prods ( Prod )
                    FOR XML PATH('')
            ), 1, 1, '')
        ) prods

Is there a reason the XML is stored in varchar? As a general rule, it is recommended you use the right [data type](https://msdn.microsoft.com/en-gb/library/hh403385.aspx?f=255&MSPPError=-2147217396) for the job. This has several benefits including: it is harder to enter invalid data and it is easier to work with (for example, there would be no need to cast the column each time). — David Rushton, Nov 28 '16 at 17:18
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — David Rushton, Nov 28 '16 at 17:19
I don't have any control over the DB, I got it like this so don't know why. — learning..., Nov 28 '16 at 19:12

Alan Burstein · Accepted Answer · 2016-11-28T19:28:35.267

Here's one approach:

-- Sample Data
DECLARE @tblX TABLE (cID int identity, cSettings xml);
INSERT @tblX (cSettings)
VALUES 
('<PreviousItem>
<string>501</string>
<string>502</string>
<string>505</string>
</PreviousItem>'),
('<PreviousItem>
<string>4433</string>
<string>5577</string>
</PreviousItem>');

-- Solution
SELECT 
  cID,
  PreviousItem = STUFF
  (
    (
      SELECT ',' + x.value('(text())[1]', 'varchar(500)')
      FROM @tblX t
      CROSS APPLY cSettings.nodes('/PreviousItem/string') x(x)
      WHERE t.cID = tx.cId
      FOR XML PATH('')
    ),1,1,''
  )
FROM @tblX tx;

Based on your sample data you have a RegistrationType that lives in your XML that you need to get... Here's a tweaked version of my first solution.

-- Updated Sample Data with RegistrationType
DECLARE @tblX TABLE (cID int identity, cSettings xml);
INSERT @tblX (cSettings)
VALUES 
('<PreviousItem>
<string>501</string>
<string>502</string>
<string>505</string>
</PreviousItem>
<RegistrationType>Type A</RegistrationType>
'),
('<PreviousItem>
<string>4433</string>
<string>5577</string>
</PreviousItem>
<RegistrationType>Type B</RegistrationType>
');

-- Solution which includes RegistrationType
SELECT 
  cID,
  PreviousItem = STUFF
  (
    (
      SELECT ',' + x.value('(text())[1]', 'varchar(500)')
      FROM @tblX t
      CROSS APPLY cSettings.nodes('/PreviousItem/string') x(x)
      WHERE t.cID = tx.cId
      FOR XML PATH('')
    ),1,1,''
  ),    
  RegistrationType = STUFF
  (
    (
      SELECT ',' + x.value('(text())[1]', 'varchar(500)')
      FROM @tblX t
      CROSS APPLY cSettings.nodes('RegistrationType') x(x)
      WHERE t.cID = tx.cId
      FOR XML PATH('')
    ),1,1,''
  )
FROM @tblX tx;

For a more accurate solution please include some DDL and consumable sample data and I can modify my solution accordingly.

Update: because your XML data is stored as text you would need to tweak my original solution like so:

-- Updated Sample Data with RegistrationType
DECLARE @tblX TABLE (cID int identity, cSettings varchar(max));
INSERT @tblX (cSettings)
VALUES 
('<PreviousItem>
<string>501</string>
<string>502</string>
<string>505</string>
</PreviousItem>
<RegistrationType>Type A</RegistrationType>
'),
('<PreviousItem>
<string>4433</string>
<string>5577</string>
</PreviousItem>
<RegistrationType>Type B</RegistrationType>
');

-- Solution which includes RegistrationType
SELECT 
  cID,
  PreviousItem = STUFF
  (
    (
      SELECT ',' + x.value('(text())[1]', 'varchar(500)')
      FROM @tblX t
      CROSS APPLY (VALUES (CAST(cSettings AS xml))) xx(xx)
      CROSS APPLY xx.xx.nodes('/PreviousItem/string') x(x)
      WHERE t.cID = tx.cId
      FOR XML PATH('')
    ),1,1,''
  ),
  RegistrationType = STUFF
  (
    (
      SELECT ',' + x.value('(text())[1]', 'varchar(500)')
      FROM @tblX t
      CROSS APPLY (VALUES (CAST(cSettings AS xml))) xx(xx)
      CROSS APPLY xx.xx.nodes('RegistrationType') x(x)
      WHERE t.cID = tx.cId
      FOR XML PATH('')
    ),1,1,''
  )
FROM @tblX tx;

Thanks for the reply! My xml is residing inside a varchar field and not xml. Your example is using .node which only works for the xml types. — learning..., Nov 28 '16 at 19:18
I updated my answer to deal with your data being stored as varchar. — Alan Burstein, Nov 28 '16 at 19:29

score 0 · Answer 2 · answered Nov 28 '16 at 20:05

Because you are dealing with text and not XML you could handle this much more efficiently using some basic T-SQL and PatExclude8K like so:

-- Updated Sample Data with RegistrationType
DECLARE @tblX TABLE (cID int identity, cSettings varchar(max));
INSERT @tblX (cSettings)
VALUES 
('<PreviousItem>
<string>501</string>
<string>502</string>
<string>505</string>
</PreviousItem>
<RegistrationType>Type A</RegistrationType>
'),
('<PreviousItem>
<string>4433</string>
<string>5577</string>
</PreviousItem>
<RegistrationType>Type B</RegistrationType>
');

-- Solution using PatExclude8K
SELECT
  cID, 
  PreviousItem     = SUBSTRING(NewString,1, LEN(NewString)-1),
  RegistrationType = SUBSTRING
  (
    cSettings,
    CHARINDEX('<RegistrationType>', cSettings)+18,
    CHARINDEX('</RegistrationType>', cSettings) - 
      (CHARINDEX('<RegistrationType>', cSettings)+18)
  )
FROM @tblX o
CROSS APPLY dbo.PatExclude8K(REPLACE(cSettings,'</string>',','), '[^0-9,]');

getting xml node as comma delimited list via select statement

2 Answers2