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
How to Subtract Two Row's Values Within Same Column Using SQL Query
How to Select Rows Where a Column Value Starts with a Certain String
Multiple SQL Update Statements in Single Query
Rounding Issue in Log and Exp Functions
Detect SQL Island Over Multiple Parameters and Conditions
Sqlite Get Name of Attached Databases
How to Query Range of Data in Db2 with Highest Performance
Datareader Ordinal-Based Lookups VS Named Lookups
Where Clause Using Values That Could Be Null
How to Change the Primary Key to Be Non-Clustered
Spark Dataframe Nested Case When Statement
Change Datatype Varchar to Nvarchar in Existing SQL Server 2005 Database. Any Issues
Query Featuring Outer Joins Behaves Differently in Oracle 12C