Returning Multiple Rows from Querying Xml Column in SQL Server 2008

Returning multiple rows from querying XML column in SQL Server 2008

Look at the .nodes() method in Books Online:

DECLARE @r TABLE (AliasesValue XML)
INSERT INTO @r
SELECT '<aliases> <alias> <aliasType>AKA</aliasType> <aliasName>Pramod Singh</aliasName> </alias> <alias> <aliasType>AKA</aliasType> <aliasName>Bijoy Bora</aliasName> </alias> </aliases> '

SELECT c.query('data(aliasType)'), c.query('data(aliasName)')
FROM @r r CROSS APPLY AliasesValue.nodes('aliases/alias') x(c)

Returning multiple rows from querying XML column in SQL Server (Revisited)

You don't need to add another CROSS APPLY just to get a different level from the XML structure. Just specify the full path in the .value() function, relative to the path specified in the .nodes() function:

DECLARE @XML_In XML = '
<ROOT>
<PROCESS_RESULT>
<CATEGORY>ABC</CATEGORY>
<STATUS>ERROR</STATUS>
<PROCESS_RESULT_MSG>
<MESSAGE_TEXT>ABC Process Category Error</MESSAGE_TEXT>
</PROCESS_RESULT_MSG>
</PROCESS_RESULT>
<PROCESS_RESULT>
<CATEGORY>XYZ</CATEGORY>
<STATUS>ERROR</STATUS>
<PROCESS_RESULT_MSG>
<MESSAGE_TEXT>XYZ Process Category Error</MESSAGE_TEXT>
</PROCESS_RESULT_MSG>
</PROCESS_RESULT>
</ROOT>'

DECLARE @XMLTab TABLE ( MyXMLTable XML)
INSERT INTO @XMLTab ( MyXMLTable ) VALUES( @XML_In )

SELECT tab.col.query('data(CATEGORY)') AS [CATEGORY],
tab.col.query('data(STATUS)') AS [STATUS],
tab.col.query('data(PROCESS_RESULT_MSG/MESSAGE_TEXT)') AS [MESSAGE_TEXT]
FROM @XMLTab
CROSS APPLY
MyXMLTable.nodes('ROOT/PROCESS_RESULT') tab(col);

Returns:

CATEGORY    STATUS        MESSAGE_TEXT
--------- ----------- ------------
ABC ERROR ABC Process Category Error
XYZ ERROR XYZ Process Category Error

Also, when naming a result set field via AS, it is best to enclose it in square-brackets (as shown in the example code above).

Returning multiple rows for XML Column using Cross apply

Try it like this: Paste this into an empty query window and transfer the idea into your code:

DECLARE @xml XML=
'<DeliveryDetails>
<Sender>
<Name>Test Data</Name>
</Sender>
<Receivers>
<Receiver id="1">
<GiftVouchers>
<Voucher>ABCD</Voucher>
<Voucher>2345</Voucher>
</GiftVouchers>
</Receiver>
<Receiver id="2">
<GiftVouchers>
<Voucher>1234</Voucher>
</GiftVouchers>
</Receiver>
<Receiver id="3">
<GiftVouchers>
<Voucher>6789</Voucher>
</GiftVouchers>
</Receiver>
<Receiver id="4">
<GiftVouchers>
<Voucher>WXYZ</Voucher>
</GiftVouchers>
</Receiver>
</Receivers>
</DeliveryDetails>';
SELECT c.value('.','varchar(max)') AS Id
FROM @xml.nodes('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') x(c)

EDIT:

It shoudl suffice to go one level deeper in your nodes() and change the SELECT to value() instead of query():

Try it without your WHERE, the CROSS APPLY should do this implicitly.

SELECT c.value('.','varchar(max)') AS Id
FROM tblTransI
CROSS APPLY TransI_xmlDetails.nodes('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') x(c)
WHERE TransI_xmlDetails.exist('/DeliveryDetails/Receivers/Receiver/GiftVouchers/Voucher') = 1

xml query help returning multiple nodes

You can use the new XML data type like this assuming the XML looks like in the answer provided by openshac and YourTable has one ClientName column and one XMLCol column.

;with xmlnamespaces ('http://org.test' as p1)
select X.ClientName,
T.N.value('p1:Type[1]/@code', 'varchar(25)') as TypeCode,
T.N.value('p1:Type[1]', 'varchar(25)') as TypeValue,
T.N.value('p1:TelephoneNumber[1]', 'varchar(25)') as TelephoneNumber
from YourTable as X
cross apply X.XMLCol.nodes('/root/p1:Telephone') as T(N)

Edit

;with xmlnamespaces ('f2.co.uk/f2/1.0' as p1) 
select X.ClientReference,
T.N.value('p1:Type[1]/@code', 'varchar(25)') as TypeCode,
T.N.value('p1:Type[1]', 'varchar(25)') as TypeValue,
T.N.value('p1:TelephoneNumber[1]', 'varchar(25)') as TelephoneNumber
from #tempa as X
cross apply X.[Xml].nodes('//p1:CommunicationChannel/p1:Telephone') AS T(N)

How to query xml across multiple rows in SQLServer 2008R2

If your xml is defined in an XML column ..

DECLARE @Items AS TABLE 
(
ItemXml XML
)

-- test data with a couple rows of xml
INSERT INTO @Items(ItemXml)
VALUES ('<Items><Item>Item 1</Item><Item>Item 2</Item></Items>')
,('<Items><Item>Item 3</Item><Item>Item 4</Item></Items>')

-- the query
SELECT t.i.value('.','VARCHAR(8000)') AS Item
FROM @Items CROSS APPLY ItemXml.nodes('/Items/Item') t(i)

Will give you

Item
------
Item 1
Item 2
Item 3
Item 4

The key here is nodes() which

is useful when you want to shred an xml data type instance into
relational data. It allows you to identify nodes that will be mapped
into a new row.

SQL XML Column - Cross Apply to Produce Multiple Rows based on XML 1 to many

CROSS APPLY 
t.xmlDoc.nodes('//Flight') AS XmlData2(xmlDoc2)

Produces three rows where the context node is already set to the three Flights in turn. You just need to use . to access it.

For example XmlData2.xmlDoc2.query('.') or XmlData2.xmlDoc2.value('.', 'int') as in this db <> fiddle example

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

Thanks Ed, but I found an easier version:

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

Though from your "unnecessarily complex" example it seems worryingly simple..

Shred XML into multiple rows in SQL

The best answer I've been able to achieve is based upon this blog post by Adam Machanic:

select 
e01_01
,e02_09
from
(select
dense_rank() over (order by b_node) unique_b_node
,c_node.value('./text()[1]','varchar(max)') e02_09

from @x.nodes('/EMSDataSet/Header/Record') b(b_node)
cross apply b.b_node.nodes('./E02/E02_09') c(c_node)
) a
join
(select
dense_rank() over (order by b2.b_node) unique_b_node
,c_node.value('text()[1]','varchar(max)') e01_01
from @x.nodes('/EMSDataSet/Header/Record') b2(b_node)
cross apply b2.b_node.nodes('./E01/E01_01') c2(c_node)
) b
on a.unique_b_node = b.unique_b_node

How to retrieve multiple values of a Xml node in Sql Server 2008

Try this:

SELECT T2.Nom
FROM RechercheOffre T1
CROSS APPLY T1.Xml.nodes('/Criteres/TypesOffre/TypeOffre') AS CTT(TypeOffre)
WHERE
T2.Nom = TypeOffre.value('(.)', 'varchar(50)')

You need to use CROSS APPLY on the .nodes() XQuery function to get a pseudo list of rows of XML, and then you can pull out the individual values from the XML nodes and compare to other bits of your database.

Return Multiple Rows from Column Value

Test Data

DECLARE @T TABLE (ScheduleID INT, EmployeeID INT ,  M INT, 
Tu INT, W INT, Th INT, F INT, Sa INT, Su INT)
INSERT INTO @T VALUES
(10 ,10001 ,1 , 1 , 0 , 0 , 0 , 0 , 0),
(11 ,10001 ,0 , 0 , 0 , 1 , 0 , 0 , 0),
(12 ,10002 ,0 , 0 , 0 , 0 , 0 , 1 , 1)

Query

;WITH X AS 
(
SELECT *
FROM (
SELECT ScheduleID
,EmployeeID
,NULLIF(M , 0) AS Monday
,NULLIF(Tu, 0) AS Tuesday
,NULLIF(W , 0) AS Wednesday
,NULLIF(Th, 0) AS Thursday
,NULLIF(F , 0) AS Friday
,NULLIF(Sa, 0) AS Saturday
,NULLIF(Su, 0) AS Sunday
FROM @T
)t
UNPIVOT (Vals FOR Workday IN (Monday,Tuesday,Wednesday,Thursday
,Friday,Saturday,Sunday))up
)
SELECT ScheduleID
,EmployeeID
,Workday
FROM X
FOR XML PATH('task'), ROOT('schedules')

Result:

<schedules>
<task>
<ScheduleID>10</ScheduleID>
<EmployeeID>10001</EmployeeID>
<Workday>Monday</Workday>
</task>
<task>
<ScheduleID>10</ScheduleID>
<EmployeeID>10001</EmployeeID>
<Workday>Tuesday</Workday>
</task>
<task>
<ScheduleID>11</ScheduleID>
<EmployeeID>10001</EmployeeID>
<Workday>Thursday</Workday>
</task>
<task>
<ScheduleID>12</ScheduleID>
<EmployeeID>10002</EmployeeID>
<Workday>Saturday</Workday>
</task>
<task>
<ScheduleID>12</ScheduleID>
<EmployeeID>10002</EmployeeID>
<Workday>Sunday</Workday>
</task>
</schedules>


Related Topics



Leave a reply



Submit