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

发布于 2024-09-12 01:22:04 字数 902 浏览 1 评论 0原文

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

每当我使用以下 XML 代码时，我尝试的每个查询都不会按预期工作：我想选择 AccNumber 值。

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <AuthSoapHd xmlns="https://temp.uri.com/Mngmt/">
      <System>System1</System>
    </AuthSoapHd>
  </soap:Header>
  <soap:Body>
    <PrintList xmlns="https://temp.uri.com/Mngmt/">
      <Account>
        <AccNumber xmlns="https://temp.uri.com/Project/Object/Data">990368644</AccNumber>
      </Account>
    </PrintList>
  </soap:Body>
</soap:Envelope>

我尝试了以下多种变体但没有成功

Select [RequestXML].query('/Envelope/Body/PrintList/Account/AccNumber')
  FROM [dbo].[Table1]

原文

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:
I want to select the AccNumber value.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <AuthSoapHd xmlns="https://temp.uri.com/Mngmt/">
      <System>System1</System>
    </AuthSoapHd>
  </soap:Header>
  <soap:Body>
    <PrintList xmlns="https://temp.uri.com/Mngmt/">
      <Account>
        <AccNumber xmlns="https://temp.uri.com/Project/Object/Data">990368644</AccNumber>
      </Account>
    </PrintList>
  </soap:Body>
</soap:Envelope>

I tried multiple varations of the following with no sucess

Select [RequestXML].query('/Envelope/Body/PrintList/Account/AccNumber')
  FROM [dbo].[Table1]

分享到QQ

分享到微博

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

发布评论

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

胡大本事 2024-09-19 01:22:04

您忽略了正在发挥作用的 XML 命名空间 - 您需要注意这一点！

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap,
                   'https://temp.uri.com/Project/Object/Data' AS data,
                   'https://temp.uri.com/Mngmt/' AS mgmt)

SELECT 
   RequestXML.value('(/soap:Envelope/soap:Body/mgmt:PrintList/mgmt:Account/data:AccNumber)[1]',
                    'BIGINT') AS 'AccNumber'
FROM 
   [dbo].[Table1]

希望这能起作用！

You're ignoring the XML namespace that is in play - you need to pay attention to that!

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap,
                   'https://temp.uri.com/Project/Object/Data' AS data,
                   'https://temp.uri.com/Mngmt/' AS mgmt)

SELECT 
   RequestXML.value('(/soap:Envelope/soap:Body/mgmt:PrintList/mgmt:Account/data:AccNumber)[1]',
                    'BIGINT') AS 'AccNumber'
FROM 
   [dbo].[Table1]

That hopefully works!

回复收藏 0 原文

~没有更多了~