Cross apply ??

Question

Cross apply ??

jbalbo

SSCertifiable

Points: 7580
More actions
September 26, 2016 at 2:37 pm

#313463

Hi
I use :
CROSS APPLY Data.nodes('/Data/Delivery_x0020_Method/Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option') m(Node2)
WHERE ( Node2.value('Checked[1]', 'varchar(max)') = 'true')
On this XML
Data>
<Delivery_x0020_Method>
<Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>
<Name>Face to Face </Name>
<Checked>false</Checked>
But not sure how to setup the cross apply for this data(added level)?
<Data>
<If_x0020_Other>symptom management,coping skills</If_x0020_Other>
<Delivery_x0020_Method>
<Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>
<Name>Face to Face </Name>
<Checked>true</Checked>
Thanks

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

jbalbo (9/26/2016)
Hi
I use :
CROSS APPLY Data.nodes('/Data/Delivery_x0020_Method/Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option') m(Node2)
WHERE ( Node2.value('Checked[1]', 'varchar(max)') = 'true')
On this XML
Data>
<Delivery_x0020_Method>
<Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>
<Name>Face to Face </Name>
<Checked>false</Checked>
But not sure how to setup the cross apply for this data(added level)?
<Data>
<If_x0020_Other>symptom management,coping skills</If_x0020_Other>
<Delivery_x0020_Method>
<Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>
<Name>Face to Face </Name>
<Checked>true</Checked>
Thanks

Quick thought, this is not an additional level but only an additional node in the XML

😎

This will return a value!

USE TEEST;

GO

SET NOCOUNT ON;

DECLARE @TXML XML =

'<Data>

<If_x0020_Other>symptom management,coping skills</If_x0020_Other>

<Delivery_x0020_Method>

<Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>

<Name>Face to Face </Name>

<Checked>true</Checked>

</Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option>

</Delivery_x0020_Method>

</Data>';

SELECT @TXML

;WITH SAMPLE_DATA(TXML) AS

(

SELECT @TXML AS TXML

)

SELECT

XN.DATA.query('*')

FROM SAMPLE_DATA SD

CROSS APPLY SD.TXML.nodes('/Data/Delivery_x0020_Method/Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option') XN(DATA)

WHERE ( XN.DATA.value('(Checked/text())[1]', 'varchar(max)') = 'true');

jbalbo SSCertifiable Points: 7580 More actions · Answer 2

Shouldn't I have "If_x0020_Other" somewhere in the cross apply?

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

jbalbo (9/26/2016)
Shouldn't I have "If_x0020_Other" somewhere in the cross apply?

The If_x0020_Other does not add a level as it is the sibling of the Delivery_x0020_Method node, therefore the query is the same.

😎

Simple tree view of the XML

Data

|---If_x0020_Other <-- This node does not add a level!

'---Delivery_x0020_Method

'------Service_x005F_x0020_Note_x005F_x0020_additions_Delivery_x0020_Method_Option

|---Name

'---Checked

In the future, please post complete and valid XML snippets;-)