SQL Server Grabbing Value from XML parameter to use in later query

Question

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.

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.

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.

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:

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

marc_s · Accepted Answer · 2015-07-09 20:42:03Z

1

You need to respect the XML namespaces!

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.

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.

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:

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

answered Jul 9, 2015 at 20:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Cashion Over a year ago

Thank you very much Marc! I am familiar with namespaces, but I somehow missed that you can use a manager in MSSQL like that - I just assumed namespaces were not considered. I defined my namespaces correctly in the manager and BINGO. Thanks again!

Gottfried Lesigang Over a year ago

If you are happy with this answer, Marc would surely appreciate your up vote and/or accept of the answer.

Matt Cashion Over a year ago

I have a new account in here, so it will not let me upvote. +1 anyways for the perfect answer and the speed in which it came!

marc_s Over a year ago

@MattCashion: after another question or two, you'll have the necessary 15+ rep points to upvote and you can always come back later and upvote this response :-) :-) :-)

Collectives™ on Stack Overflow

SQL Server Grabbing Value from XML parameter to use in later query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related