filtering result set using xquery

Question

Post reply

filtering result set using xquery

kentk

SSC Rookie

Points: 37
More actions
May 27, 2011 at 8:03 am

#74667

I'm having trouble figuring out how to filter this snippet of xml. I would like to get the svcProvNbr where the userServiceCde = OASV
<dataExtract xmlns="http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS/dataExtract.xsd">
<shipment>
<location lctnSeqNbr="001">
<lctnServiceProvider>
<userServiceCde>EASV</userServiceCde>
<svcProvNbr>1470-000</svcProvNbr>
<svcProvTypeCde>A</svcProvTypeCde>
</lctnServiceProvider>
<lctnServiceProvider>
<userServiceCde>OASV</userServiceCde>
<svcProvNbr>1470-000</svcProvNbr>
<svcProvTypeCde>A</svcProvTypeCde>
</lctnServiceProvider>
</location>
</shipment>
</dataExtract>
I have tried a couple different ways but no luck
xmlalliedxml.query('declare namespace r = "http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS";
data(/r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider.userServiceCde[.="OASV"]/r:svcProvNbr)')
and
xmlalliedxml.query('declare namespace r = "http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS";
for $sp in /r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider/r:svcProvNbr
where $sp/r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider/r:userServiceCde[.="OASV"]
return
$sp')

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

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

!2Brite SSC Veteran Points: 219 More actions · Answer 1

is this what you are looking for?

@xmlalliedxml.query(

'declare namespace r = "http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS";

data(/r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider/r:svcProvNbr[parent::*/r:userServiceCde="OASV"])')

same thing just short hand version of the parent axis

@xmlalliedxml.query(

'declare namespace r = "http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS";

data(/r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider/r:svcProvNbr[../r:userServiceCde="OASV"])')

!2Brite SSC Veteran Points: 219 More actions · Answer 2

just looked again there is no need for the parent axis

select

@xmlalliedxml.query(

'declare namespace r = "http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS";

data(/r:dataExtract/r:shipment/r:location[@lctnSeqNbr=sql:variable("@vcSegmentNo")]/r:lctnServiceProvider[./r:userServiceCde="OASV"]/r:svcProvNbr)')

kentk SSC Rookie Points: 37 More actions · Answer 3

thanks !2Brite that's exactly what I was looking for...