如何从 SQL XML 查询中选择标签？

发布于 2024-07-24 07:45:17 字数 778 浏览 6 评论 0原文

如何在 MS SQL 中检索 XML 字段中的字段？

每当我使用此 XML 代码时，我尝试的每个查询都无法按预期工作：

<soap:Envelope xmlns:xsi="[URI]" xmlns:xsd="[URI]" xmlns:soap="[URI]">
  <soap:Body>
    <RunPackage xmlns="[URI]">
      <xmlDoc>
        <Request>
          <SubscriberCode>76547654</SubscriberCode>
          <CompanyCode></CompanyCode>
        </Request>
      </xmlDoc>
    </RunPackage>
  </soap:Body>
</soap:Envelope>

我不知道如何引用前两个标记。我尝试过

SELECT TransactionID, T2.Loc.query('data(Request/SubscriberCode)') as 'SubscriberCode'
FROM   TempWorksRequest
CROSS APPLY RequestXML.nodes('soap:Envelope/soap:Body/RunPackage/xmlDoc') as T2(Loc)

但没有运气。

原文

How can I retrieve the fields within an XML field in MS SQL?

Every query I try does not work as intended whenever I use this XML code:

<soap:Envelope xmlns:xsi="[URI]" xmlns:xsd="[URI]" xmlns:soap="[URI]">
  <soap:Body>
    <RunPackage xmlns="[URI]">
      <xmlDoc>
        <Request>
          <SubscriberCode>76547654</SubscriberCode>
          <CompanyCode></CompanyCode>
        </Request>
      </xmlDoc>
    </RunPackage>
  </soap:Body>
</soap:Envelope>

I don't know how to reference the first two tags. I've tried

SELECT TransactionID, T2.Loc.query('data(Request/SubscriberCode)') as 'SubscriberCode'
FROM   TempWorksRequest
CROSS APPLY RequestXML.nodes('soap:Envelope/soap:Body/RunPackage/xmlDoc') as T2(Loc)

With no luck.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

狼亦尘 2024-07-31 07:45:17

您需要在 XQuery 操作中声明 XML 命名空间（本例中为“soap”，再加上节点和下面的任何内容的另一个命名空间）：

SELECT 
   TransactionID, 
   T2.Loc.query('declare namespace ns="[URI1]";data(ns:Request/ns:SubscriberCode)') 
     as 'SubscriberCode'
FROM   
   TempWorksRequest
CROSS APPLY 
   RequestXML.nodes('declare namespace soap="[URI]";
                     declare namespace ns="[URI1]";
                     soap:Envelope/soap:Body/ns:RunPackage/ns:xmlDoc') as T2(Loc)

[URI1] 需要是在 < code>标记。

马克

You need to declare the XML namespaces ("soap" in this case, plus another one for the node and anything below) in your XQuery operations:

SELECT 
   TransactionID, 
   T2.Loc.query('declare namespace ns="[URI1]";data(ns:Request/ns:SubscriberCode)') 
     as 'SubscriberCode'
FROM   
   TempWorksRequest
CROSS APPLY 
   RequestXML.nodes('declare namespace soap="[URI]";
                     declare namespace ns="[URI1]";
                     soap:Envelope/soap:Body/ns:RunPackage/ns:xmlDoc') as T2(Loc)

[URI1] needs to be the URI that's defined on the <RunPackage> tag.

Marc

回复收藏 0 原文

~没有更多了~