CODEWITHSUNDEEP
sql-serverxmlxpathxquery
Manan Kapoor
Manan Kapoor

Reputation: 327

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

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.

Upvotes: 0

Views: 387

Answers (1)

Dale K
Dale K

Reputation: 27202

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'

Upvotes: 2

Related Questions