Retrieve multiple records from an XML Node present in the Table Column

Question

I am trying to retrieve all the GUIDs present in a particular XML Node. The XML is stored already in a column in the table. This is how it looks like

<ArrayOfString>
  <string>52ddf406-00d9-v029-d748-2ee6958bdc95</string>
  <string>8902ce52-00d9-vda9-afe9-0b3fa35f88b2</string>
  <string>8902ce52-00d9-v919-0cb5-0b3fa1672fb3</string>
  <string>8902ce52-00d9-v064-8e24-0b401390a1a7</string>
</ArrayOfString>

This is what I am trying to do

SELECT ID, Cats = XC.value('(string)[1]', 'varchar(50)') 
FROM TableName 
CROSS APPLY CAST(TableName AS XML).nodes('/ArrayOfString/string') AS XT(XC)

I receive the following error

Incorrect syntax near the keyword 'AS'.

I am able to retrieve any particular record. But NOT all the records at once.

Dale K · Accepted Answer · 2021-03-04T19:28:08.623

You've already got the <string> node, so you don't want to query it further. Instead query the only element of the node.

(And your syntax for obtaining the nodes was a bit off).

declare @Test table (id int, data xml);

insert into @Test (id, data)
values (1,'<ArrayOfString>
  <string>52ddf406-00d9-v029-d748-2ee6958bdc95</string>
  <string>8902ce52-00d9-vda9-afe9-0b3fa35f88b2</string>
  <string>8902ce52-00d9-v919-0cb5-0b3fa1672fb3</string>
  <string>8902ce52-00d9-v064-8e24-0b401390a1a7</string>
</ArrayOfString>');

SELECT ID, Cats = XT.XC.value('text()[1]', 'varchar(50)') 
FROM @Test T
CROSS APPLY T.data.nodes('/ArrayOfString/string') AS XT (XC);

Returns

ID	Cats
1	52ddf406-00d9-v029-d748-2ee6958bdc95
1	8902ce52-00d9-vda9-afe9-0b3fa35f88b2
1	8902ce52-00d9-v919-0cb5-0b3fa1672fb3
1	8902ce52-00d9-v064-8e24-0b401390a1a7

To get a single row (per id) use string_agg if you are using SQL Server 2017+:

SELECT T.ID, string_agg(XT.XC.value('text()[1]', 'varchar(50)'), ',') Cats 
FROM @Test T
CROSS APPLY T.data.nodes('/ArrayOfString/string') AS XT (XC)
GROUP BY T.ID;

Returns:

id	Cats
1	'52ddf406-00d9-v029-d748-2ee6958bdc95,8902ce52-00d9-vda9-afe9-0b3fa35f88b2,8902ce52-00d9-v919-0cb5-0b3fa1672fb3,8902ce52-00d9-v064-8e24-0b401390a1a7'

Thanks. That worked. I actually needed to get all these records as a single comma-separated string rather than different rows. Sorry I didn't update that in the question. — Manan Kapoor, Mar 04 '21 at 09:16
@Charlieface `text()[1]` actually :) I looked at that, but it didn't appear to need it. Is there an issue using `.[1]`? — Dale K, Mar 04 '21 at 19:11
Performance mainly, https://stackoverflow.com/a/40990071/14868997 `.` causes a nested loop and needs an extra check that the node type is `xsd:string` — Charlieface, Mar 04 '21 at 19:23

Retrieve multiple records from an XML Node present in the Table Column

1 Answers1