在 xml sql 列上使用 xquery 过滤结果集

Question

我无法弄清楚如何过滤此 xml 片段。我想获得 svcProvNbr 其中 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>

我尝试了几种不同的方法，但没有

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

运气

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

Answer 1

试试这个：

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"

我修改了您的 XML，以便可以将两个条目分开，并且此 SELECT 语句确实返回 1470-001 作为值。

更新：要从表中的列执行此操作，请使用以下代码：

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

Answer 2

除了 marc_s 提供的之外，您可能还需要考虑使用 CROSS APPLY
TableColumn.nodes('/') as t(c) 位于 FROM 和 WHERE 之间（TableColumn 是包含 XML 的列的名称，然后是括号内的路径）。