CODEWITHSUNDEEP
sqlsql-serverxmlstored-procedures
Matt Cashion
Matt Cashion

Reputation: 13

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

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>

Upvotes: 1

Views: 372

Answers (1)

marc_s
marc_s

Reputation: 755541

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.

Upvotes: 1

Related Questions