filtering result set using xquery on xml sql column

Question

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')

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, May 27 '11 at 16:32

marc_s · Answer 1 · 2011-05-27T20:33:51.847

Try this:

DECLARE @input XML = '<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-001</svcProvNbr>
       <svcProvTypeCde>A</svcProvTypeCde>
     </lctnServiceProvider>
   </location>
 </shipment>
</dataExtract>'

;WITH XMLNAMESPACES('http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS' AS ns)
SELECT
    @input.value('(ns:dataExtract/ns:shipment/ns:location/ns:lctnServiceProvider[ns:userServiceCde="OASV"]/ns:svcProvNbr)[1]', 
                 'varchar(50)') AS 'Service Provider Number"

I modified your XML so I can keep the two entries apart, and this SELECT statement does indeed return 1470-001 as the value.

Update: to do this from a column in a table, use this code:

;WITH XMLNAMESPACES('http://dtd-sirvahub.sirva.com/xml/SCHEMA/ATS' AS ns)
SELECT
    XmlAllied.value('(ns:dataExtract/ns:shipment/ns:location/ns:lctnServiceProvider[ns:userServiceCde="OASV"]/ns:svcProvNbr)[1]', 
                 'varchar(50)') AS 'Service Provider Number"
FROM 
    dbo.AutoExtract
WHERE
    (some condition here)

how would i do this from a xml column, not a variable? In this case my xml is in a column called xmlallied on a table called autoextract — bubk, May 27 '11 at 18:31
@bubk: updated my answer with a sample for doing it from a column in a table — marc_s, May 27 '11 at 20:34

score 0 · Answer 2 · answered Sep 26 '12 at 14:36

0

In addition to what marc_s gave, you might want to consider using CROSS APPLY TableColumn.nodes('/') as t(c) between FROM and WHERE (with TableColumn being the name of column containing the XML and then the path inside the parenthesis).

answered Sep 26 '12 at 14:36

N1tr0

485
2
6
24

filtering result set using xquery on xml sql column

2 Answers2