?? Cross Apply error...

Question

Post reply

?? Cross Apply error...

jbalbo

SSCertifiable

Points: 7583
More actions
April 7, 2015 at 12:16 pm

#316411

Hi
I'm trying to view two different parts of an XML field and getting the error:
The correlation name '' is specified multiple times in a FROM clause ??
Thanks in Advance
Joe
SELECT
Node2.value('Name[1]','varchar(max)') as Name2,
Node2.value('Checked[1]','varchar(max)') AS Checked,
Node.value('Name[1]','varchar(max)') as Name
FROM EmbeddedDocuments X
CROSS APPLY Data.nodes('/Data/Service_x0020_Components/Service_x005F_x0020_Note_x005F_x0020_additions_Service_x0020_Components_Option') (Node2)
CROSS APPLY data.nodes('Data/Delivery_x0020_Method/Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option') (Node)
WHERE X.ParentObject=1087259

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Note this query (created simplified XML data since there was no DDL to work with).

DECLARE @x TABLE (id int identity, data xml);

INSERT @x VALUES('<xxx><yyy>123</yyy><zzz>999</zzz></xxx>');

SELECT

id,

n2.value('xxx[1]/yyy[1]', 'varchar(10)'),

n1.value('.[1]', 'varchar(10)')

FROM @x x

CROSS APPLY data.nodes('/') node2(n2)

CROSS APPLY n2.nodes('xxx/zzz') node1(n1)

WHERE id = 1;

Edit: duh, did not paste my example :blush:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Here's a better example using multiple nodes on the second apply

DECLARE @x TABLE (id int identity, data xml);

INSERT @x VALUES

('

<xxx blah="xml1">

<yyy>123</yyy>

<yyy>456</yyy>

<yyy>789</yyy>

</xxx>

'),

('

<xxx blah="xml2">

<yyy>999</yyy>

<yyy>888</yyy>

</xxx>

');

SELECT

id,

n2.value('(@blah)[1]', 'varchar(10)') as blah_attrib,

n1.value('.[1]', 'varchar(10)') as child_node_yyy,

data as xml_data

FROM @x x

CROSS APPLY data.nodes('/xxx') node2(n2)

CROSS APPLY n2.nodes('yyy') node1(n1)

WHERE id < 10;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001