CODEWITHSUNDEEP
sql-serverxml
AFF
AFF

Reputation: 117

TSQL and Soap Envelope

with the following query Im trying to Read MessageID and DocPurpose. but only Message ID is showing fine.... Can anyone tellme what Im missing? It will be apreciated

Declare @XmlData XML =
'<Envelope xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message">
  <Header>
    <MessageId>e96d3ee4-5cca-4b3e-be60-d1284a0fb02f</MessageId>
    <Company>CompanyX</Company>
    <Action>http://schemas.microsoft.com/dynamics/2008/01/services/ReturnOrderInService/create</Action>
    <ConversationId>320034ab-f5ee-41b0-ba35-e0669c2bf1b8</ConversationId>
    <RequestMessageId>e96d3ee4-5cca-4b3e-be60-d1284a0fb02f</RequestMessageId>
  </Header>
  <Body>
    <MessageParts>
      <ReturnOrderIn xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/ReturnOrderIn">
        <DocPurpose xmlns="">Original</DocPurpose>
        <SenderId xmlns="">Xcorp</SenderId>
        <SalesTable class="entity" xmlns="">
          <_DocumentHash>63cf8580-92fe-4b17-b15c-8e619eecf71f</_DocumentHash>
          <CurrencyCode>USD</CurrencyCode>
          <CustAccount>147854</CustAccount>
          <LanguageId>en-us</LanguageId>
          <Reservation>None</Reservation>
          <ReturnDeadline>2014-10-10</ReturnDeadline>
          <ReturnReasonCodeId>Reason</ReturnReasonCodeId>
          <SalesName>Stores</SalesName>
          <SalesLine class="entity">
            <DefaultDimension>
              <Values xmlns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes">
                <Value>
                  <Name>Site</Name>
                  <Value>VN</Value>
                </Value>
              </Values>
            </DefaultDimension>
            <ExpectedRetQty>-5</ExpectedRetQty>
            <ItemId>Else</ItemId>
            <Name>Something</Name>
            <PriceUnit>11.00</PriceUnit>
            <SalesQty>-15.00</SalesQty>
          </SalesLine>
        </SalesTable>
      </ReturnOrderIn>
    </MessageParts>
  </Body>
</Envelope>'

Declare @MessageId varchar(50)
    ;WITH XMLNAMESPACES('http://schemas.microsoft.com/dynamics/2011/01/documents/Message' As a, 
                    'http://schemas.microsoft.com/dynamics/2008/01/documents/ReturnOrderIn' As b,
                        default 'http://schemas.microsoft.com/dynamics/2011/01/documents/Message')

Select @MessageId =MessageId  from 
    (SELECT 
            t.c.value('a:MessageId[1]', 'varchar(50)') MessageId
    FROM    @XmlData.nodes('/Envelope/Header') AS t(c)) x

Declare @DocPurpose varchar(50)
    ;WITH XMLNAMESPACES('http://schemas.microsoft.com/dynamics/2011/01/documents/Message' As a, 
                    'http://schemas.microsoft.com/dynamics/2008/01/documents/ReturnOrderIn' As b,
                        default 'http://schemas.microsoft.com/dynamics/2011/01/documents/Message')

    Select @DocPurpose = DocPurpose from
    (SELECT 
                t.d.value('b:DocPurpose[1]', 'varchar(50)') DocPurpose
        FROM    @XmlData.nodes('Envelope/body/MessageParts/b:ReturnOrderIn') AS t(d))z
    Print 'MessageID>>>>>>' + @MessageId 
    Print @DocPurpose

Upvotes: 1

Views: 129

Answers (1)

har07
har07

Reputation: 89295

<DocPurpose xmlns=""> node is in empty namespace, not the default namespace. Since we can't declare a prefix to map empty namespace URI, just don't declare a default in ;WITH XMLNAMESPACES block. Try this way :

Declare @DocPurpose varchar(50)
;WITH XMLNAMESPACES
        ('http://schemas.microsoft.com/dynamics/2011/01/documents/Message' As a, 
         'http://schemas.microsoft.com/dynamics/2008/01/documents/ReturnOrderIn' As b)

Select @DocPurpose = DocPurpose from
(
    SELECT t.d.value('DocPurpose[1]', 'varchar(50)') DocPurpose
    FROM @XmlData.nodes('a:Envelope/a:Body/a:MessageParts/b:ReturnOrderIn') AS t(d)
)z

Small thing but matter, you need uppercase B for Body.

Upvotes: 2

Related Questions