XQuery Question

When I run the following script....

declare @d xml

set @d='< xml version="1.0" encoding="UTF-8" >

<Subsidiaries>

<Subsidiary id="0">

<TypeMove id="0">

<Orders Type="1">

<OESourceID id="1">

<StageID id="10300">ODS.ProcessOrderTotals</StageID>

<StageID id="10400">ODS.ProcessOrderTotals1</StageID>

<StageID id="10500">ODS.ProcessOrderPayment</StageID>

<StageID id="10600">ODS.ProcessOrderItems</StageID>

</OESourceID>

</Orders>

</TypeMove>

</Subsidiary>

<Subsidiary id="1">

<TypeMove id="0">

<Orders Type="1">

<OESourceID id="1">

<StageID id="10400">ODS.ProcessOrderTotals2</StageID>

<StageID id="10500">ODS.ProcessOrderPayment</StageID>

<StageID id="10600">ODS.ProcessOrderItems</StageID>

</OESourceID>

</Orders>

</TypeMove>

</Subsidiary>

</Subsidiaries>'

select @d.query('/Subsidiaries/Subsidiary[@id=max(@id)]/TypeMove[@id=0]/Orders[@Type=1]/OESourceID[@id=1]/StageID[@id=10400]/text()')

I get the text from Subsidiary 0 and 1 for stage id 10400. I was exepecting only the text from Subsidiary 1.

any ideas

gary

Answer this question

XQuery Question

surajguru
The query you want is:

select @d.query('/Subsidiaries/Subsidiary[@id=max(/Subsidiaries/Subsidiary/@id)]/TypeMove[@id=0]/Orders[@Type=1]/OESourceID[@id=1]/StageID[@id=10400]/text()')

The max() is expecting a node set.
Robertwell
Thanks Lyle,

if I just want to find the text() from the subsidiary I am interested in (ie. sub = 3) or if that value is missing is, the value from the default sub (id =0) how would I do that

Gary
XQuery Question

Answer this question