0

I have the following XML stored in a XML column of a table:

<MessageResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <MessageContainer>
    <DocumentType xmlns="urn:www:bebe:de:b2b:v01">Testdata</DocumentType>
    <Payload xmlns="urn:www:bebe:de:b2b:v01">
      <Testdata xmlns:ns0="un:unece:260:data:ron_data:v1">
        <PayloadResponseEvent>
          <Identification>2970729807</Identification>
          <StatusType listAgencyIdentifier="6">accepted</StatusType>
          <OriginalDocument>2769262</OriginalDocument>
        </PayloadResponseEvent>
        <PayloadResponseEvent>
           <Identification>2970729809</Identification>
          <StatusType listAgencyIdentifier="6">rejected</StatusType>
          <OriginalDocument>2769269</OriginalDocument>
        </PayloadResponseEvent>
      </Testdata>
    </Payload>
  </MessageContainer>
</MessageResponse>

I want to create a select in which I get columns based on PayloadResponseEvent like:

Identification        StatusType    OriginalDocument
----------------------------------------------------
2970729807            accepted      2769262
2970729809            rejected      2769269

I have tried many different SQL / XML statements based on Nodes and values but cannot find an easy way of getting this data. Also namespaces are getting in the way.

Can anybody provide a solution to this?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cip123
  • 3
  • 1
  • 2
    This is not a codewriting service. Show us what exactly have you tried and what the problem is. – MarthyM Aug 21 '17 at 16:16

1 Answers1

3
WITH XMLNAMESPACES (DEFAULT 'urn:www:bebe:de:b2b:v01')
SELECT 
    d.p.value('./Identification[1]', 'BIGINT') AS [Identification]
    ,d.p.value('./StatusType[1]', 'NVARCHAR(100)') AS [StatusType]
    ,d.p.value('(./StatusType/@listAgencyIdentifier)[1]', 'int') AS [ListAgencyIdentifier]
    ,d.p.value('./OriginalDocument[1]', 'BIGINT') AS [OriginalDocument]
FROM [yourtable] [t]
CROSS APPLY [t].[yourcolumn].nodes('//PayloadResponseEvent') AS d(p)
Matthew Whited
  • 22,160
  • 4
  • 52
  • 69
  • Thank you very much! That is exactly what I needed. I gues the problem was the XMLNAMESPACES and the nodes('//PayloadResponseEvent'). – cip123 Aug 22 '17 at 08:27