SQL Server从XML参数中获取值以在以后的查询中使用

I am really new to SQL Server and stored procedures to begin with. I need to be able to parse an incoming XML file for a specific element's value and compare/save it later in the procedure.

我是SQL Server和存储过程的新手。我需要能够解析传入的XML文件以获取特定元素的值,并在稍后的过程中比较/保存它。

I have a few things stacked against me. One the Element I need is buried deeply inside the document. I have had no luck in searching for it by name using methods similar to this:

我有一些东西堆在我身上。我需要的一个元素深埋在文档中。我没有运气使用类似的方法通过名称搜索它:

    select CurrentBOD = c.value('(local-name(.))[1]', 'VARCHAR(MAX)'),
                c.value('(.)[1]', 'VARCHAR(MAX)') from @xml.nodes('PutMessage/payload/content/AcknowledgePartsOrder/ApplicationArea/BODId') as BODtable(c)

It always returns null.

它总是返回null。

So, I am trying something similar to this:

所以,我正在尝试类似的东西:

    declare @BODtable TABLE(FieldName VARCHAR(MAX),
                    FieldValue VARCHAR(MAX))
    SELECT
    FieldName = nodes.value('local-name(.)', 'varchar(50)'),
    FieldValue = nodes.value('(.)[1]', 'varchar(50)')
    FROM
    @xml.nodes('//*') AS BODtable(nodes)

    declare @CurrentBOD VARCHAR(36)
    set @CurrentBOD = ''

    SET @CurrentBOD = (SELECT FieldValue from @BODtable WHERE FieldName = 'BODId')

This provides me the list of node names and values correctly (I test this in a query and BODtable has all elements listed with the correct values), but when I set @CurrentBOD it comes up null.

这为我提供了正确的节点名称和值列表(我在查询中对此进行了测试,BODtable列出了所有使用正确值的元素),但是当我设置@CurrentBOD时,它会出现null。

Am I missing an easier way to do this? Am I messing these two approaches up somehow?

我错过了一种更简单的方法吗?我是不是以某种方式弄乱了这两种方法?

Here is a part of the xml I am parsing for reference:

这是我正在解析参考的xml的一部分:

<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"           xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/03/addressing"     xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-     secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-     wss-wssecurity-utility-1.0.xsd">
      <soap:Header>
<payloadManifest xmlns="???">
  <c contentID="Content0" namespaceURI="???" element="AcknowledgePartsOrder" version="4.0" />
</payloadManifest>
<wsa:Action>http://www.starstandards.org/webservices/2005/10/transport/operations/PutMessage</wsa:Action>
<wsa:MessageID>uuid:df8c66af-f364-4b8f-81d8-06150da14428</wsa:MessageID>
<wsa:ReplyTo>
  <wsa:Address>http://schemas.xmlsoap.org/ws/2004/03/addressing/role/anonymous</wsa:Address>
</wsa:ReplyTo>
<wsa:To>???</wsa:To>
<wsse:Security soap:mustUnderstand="1">
  <wsu:Timestamp wsu:Id="Timestamp-bd91e76f-c212-4555-9b23-f66f839672bd">
    <wsu:Created>2013-01-03T21:52:48Z</wsu:Created>
    <wsu:Expires>2013-01-03T21:53:48Z</wsu:Expires>
  </wsu:Timestamp>
  <wsse:UsernameToken xmlns:wsu="???" wsu:Id="???">
    <wsse:Username>???</wsse:Username>
    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">???</wsse:Password>
    <wsse:Nonce>???</wsse:Nonce>
    <wsu:Created>2013-01-03T21:52:48Z</wsu:Created>
  </wsse:UsernameToken>
</wsse:Security>
  </soap:Header>
  <soap:Body>
<PutMessage xmlns="??????">
  <payload>
    <content id="???">
      <AcknowledgePartsOrder xmlns="???" xmlns:xsi="???" xsi:schemaLocation="??? ???" revision="???" release="???" environment="???n" lang="en-US" bodVersion="???">
        <ApplicationArea>
          <Sender>
            <Component>???</Component>
            <Task>???</Task>
            <ReferenceId>???</ReferenceId>
            <CreatorNameCode>???</CreatorNameCode>
            <SenderNameCode>???</SenderNameCode>
            <DealerNumber>???</DealerNumber>
            <PartyId>???</PartyId>
            <LocationId />
            <ServiceId />
          </Sender>
          <CreationDateTime>2013-01-03T21:52:47</CreationDateTime>
          <BODId>71498800-c098-4885-9ddc-f58aae0e5e1a</BODId>
          <Destination>
            <DestinationNameCode>???</DestinationNameCode>

1 个解决方案

#1

You need to respect the XML namespaces!

您需要尊重XML命名空间!

First of all, your target XML node <BODId> is inside the <soap:Envelope> and <soap:Body> tags - both need to be included in your selection.

首先,您的目标XML节点位于和标记内 - 两者都需要包含在您的选择中。

Secondly, both the <PutMessage> as well as the <AcknowledgePartsOrder> nodes appear to have default XML namespaces (those xmlns=.... without a prefix) - and those must be respected when you select your data using XPath.

其次, 和节点似乎都有默认的XML命名空间(那些没有前缀的xmlns = ....) - 当您使用XPath选择数据时,必须遵守这些命名空间。

So assuming that <PutMessage xmlns="urn:pm"> and <AcknowledgePartsOrder xmlns="urn:apo"> (those are just guesses on my part - replace with the actual XML namespaces that you haven't shown use here), you should be able to use this XPath to get what you're looking for:

所以假设和 (这些只是我自己的猜测 - 用你在这里没有显示的实际XML命名空间替换),你应该能够使用此XPath来获取您正在寻找的内容:

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 
                    'urn:pm' AS ns, 'urn:apo' AS apo)
SELECT
    XC.value('(apo:BODId)[1]', 'varchar(100)')
FROM 
    @YourXmlVariable.nodes('/soap:Envelope/soap:Body/ns:PutMessage/ns:payload/ns:content/apo:AcknowledgePartsOrder/apo:ApplicationArea') AS XT(XC)

This does return the expected value (71498800-c098-4885-9ddc-f58aae0e5e1a) in my case.

在我的情况下,这确实返回了预期值(71498800-c098-4885-9ddc-f58aae0e5e1a)。

#1