Process Element XML and Attribute XML in single node

By on May 9th, 2014

Process Element XML and Attribute XML in single node.


DECLARE @T VARCHAR(400)
SET @T = '<Log DeviceId="MainDevice001">
  <Info UId="100000" Date="05/05/2014">
    <Err>Error1</Err>
  </Info>
  <Info UId="100001" Date="05/05/2014">
    <Err>Error2</Err>
  </Info>
</Log>'
DECLARE @Errorlogsxml AS XML
SET @Errorlogsxml = Cast(@T AS XML)
SELECT TAB1.COL1.value('@UId', 'VARCHAR(100)') AS Uid,
       X.Deviceid AS Deviceid,
       TAB1.COL1.value('@Date', 'DATETIME') AS Date,
       TAB1.COL1.value('(Err)[1]', 'VARCHAR(100)') AS Error
FROM   (SELECT TAB.COL.value('@DeviceId', 'VARCHAR(100)') AS Deviceid
        FROM   @Errorlogsxml.nodes('/Log') AS Tab(COL)) X
       CROSS APPLY @Errorlogsxml.nodes('/Log/Info') AS Tab1(COL1)
Categorized under: TSQL, XML.
Tagged with: .