Geek Answers Handbook

Getting multiple records from xml column with value () in SQL Server

This SQL returns only the first element of an Activity. How to choose them? If I delete [1] in the request, I get the error message "value () requires a singleton".

DECLARE @myDoc xml SET @myDoc = '<Root> <Activities> <Activity>This is activity one</Activity> <Activity>This is activity two</Activity> <Activity>This is activity three</Activity> </Activities> </Root>' SELECT @myDoc.value('(/Root/Activities/Activity)[1]', 'varchar(100)' )
+7
xquery sql-server-2005
source share
3 answers

Thanks Ed, but I found a lighter version:

 SELECT TCvalue('.', 'varchar(100)') as activity FROM @myDoc.nodes('(/Root/Activities/Activity)') as T(C)

Although from your "overly complex" example, it seems rather alarming.

+15
source share

It works, but it seems unnecessarily complicated. Perhaps there will be an easier way.

  DECLARE @myDoc xml SET @myDoc = '<Root> <Activities> <Activity>This is activity one</Activity> <Activity>This is activity two</Activity> <Activity>This is activity three</Activity> </Activities> </Root>' SELECT activity.VALUE('(//Activity)[1]','varchar(100)') AS activity FROM ( SELECT NewTable.activity.query('.') AS activity FROM (SELECT 1 AS col1) AS t CROSS APPLY @myDoc.nodes('(/Root/Activities/Activity)') AS NewTable(activity) ) AS x
+2
source share

Here is another situation and solution: I was looking for this situation when there are two elements that need to be selected with a single query.

 CREATE TABLE #Table1 (ID INT IDENTITY(1,1),XMLDoc XML) INSERT INTO #Table1 VALUES (' <bookstore> <name>Bookstore1</name> <location>Location1</location> <book> <title>Titile1</title> <price>40</price> </book> </bookstore>') INSERT INTO #Table1 VALUES (' <bookstore> <name>Bookstore2</name> <location>Location2</location> <book> <title>Titile2</title> <price>50</price> </book> </bookstore>') SELECT ID, Tcvalue('title[1]','varchar(50)') AS 'BookTitile', Tcvalue('price[1]','decimal(18,2)') AS 'Price' FROM #Table1 CROSS APPLY #Table1.XMLDoc.nodes('/bookstore/book') T(c) DROP TABLE #Table1

You can change this as required to include the XMLNamespaces. The solution was initially found at: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35e75e32-9ffb-4a30-8637-2cc928554763/selecting-multiple-values-from-multiple-rows-of- xml? forum = sqlxml

0
source share

More articles:


All Articles