Exchamples: T-SQL select from XML

tsql_xml_exchample.sql

declare @xml xml;
set @xml = '
<data>
  <row UID="88888888" Date="2014-05-06" DateTime="2014-10-22T13:02:10.17" Value="123" />
  <row UID="88883488" Date="2014-10-11" Value="153" />
  <row UID="88883488" Date="2014-10-11" DateTime="20141022 13:02" Value="153" />
</data>
'
-- Case 1: xml.nodes
SELECT
  tbl.col.value('@UID',     'varchar(8)')     [UID],
  tbl.col.value('@Date',    'date')           [Date],
  tbl.col.value('@DateTime','smalldatetime')  [DateTime],
  tbl.col.value('@Value',   'int')            [Value]
FROM @xml.nodes('/data/row') tbl(col)

-- Case 2: use sp_xml_preparedocument
set @xml = '
<data TransactionId="125">
  <row UID="88888888" Date="2014-05-06" DateTime="2014-10-22T13:02:10.17" Value="123" />
  <row UID="88883488" Date="2014-10-11" Value="153">
    <txt>Another text!</txt>
  </row>
</data>'

-- Prepare XML document
declare @docHandle int
exec sp_xml_preparedocument @docHandle output, @xml

-- Read from XML (read from tag 'row' and from parent tage 'data').
select * from openxml(@docHandle, '/data/row') with (
  [UID]           varchar(8),
  [Date]          date,
  [DateTime]      smalldatetime,
  [Value]         int,
  [TransactionId] bigint       '../@TransactionId', -- Get from parent
  [InnerXml]      varchar(max) '@mp:xmltext'        -- Current node as InnerXml text
)

-- Get data from tag
select * from openxml(@docHandle, '/data/row', 2) with (
  id  varchar(8) 'UID',
  txt varchar(max)
)

-- Remove opened document
exec sp_xml_removedocument @docHandle

-- Case 3: Select data from fields type of XML
declare @tmp1 table (id int, XmlData xml);
insert into @tmp1 values
(1, '<data Id="1" Name="Mon">Apple</data>'    ),
(2, '<data Id="3" Name="Sun">Microsoft</data>'),
(3, '<data Id="5" Name="Fri">Google</data>'   );

SELECT
  id,
  XmlData.value('(/data/@Id)[1]',   'int'        ) as [xml_id],
  XmlData.value('(/data/@Name)[1]', 'varchar(10)') as [xml_Name],
  XmlData.value('(/data/.)[1]',     'varchar(10)') as [xml_Val]
FROM @tmp1

-- Select from multilevel
declare @tmp2 table (id int not null identity(1,1), XmlData xml);
insert into @tmp2 (XmlData) values
('<data Id="1" Name="Mon"><sub sa="tes1">Apple</sub></data>'    ),
('<data Id="3" Name="Sun"><sub sa="tes2">Microsoft</sub></data>'),
('<data Id="5" Name="Fri"><sub sa="tes3">Google</sub></data>'   );

SELECT
  id,
  XmlData.value('(/data/@Id)[1]',     'int'        ) as [xml_id],
  XmlData.value('(/data/@Name)[1]',   'varchar(10)') as [xml_Name],
  XmlData.value('(/data/sub/@sa)[1]', 'varchar(10)') as [xml_sub_attr],
  XmlData.value('(/data/sub/.)[1]',   'varchar(10)') as [xml_Val]
FROM @tmp2