CODEWITHSUNDEEP
sqlxmlxquery
cip123
cip123

Reputation: 3

Read XML nodes as rows in SQL Server

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

Upvotes: 0

Views: 706

Answers (1)

Matthew Whited
Matthew Whited

Reputation: 22443

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)

Upvotes: 3

Related Questions