Pages

Monday, March 20, 2017

Use SQL to read XML data - Example


There are a few ways to read XML data using SQL and following example demonstrate how to read a simple XML file in tabular format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
DECLARE @MyXML XML
-- SET @MyXML = (SELECT CONVERT(xml, BulkColumn, 2) xml 
-- FROM OPENROWSET(Bulk 'C:\sample.XML', SINGLE_BLOB) [rowsetresults])
SET @MyXML = '
<Order xmlns="XMLSchema">
  <Database>
    <Tables>
      <Table Name="Product">
        <Files>
          <File FileName="Product.txt" NumberOfRows="17" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="ATC_1_CD" Length="30" DataType="nvarchar" />
    <Column Name="IMS_PROD_SHRT_NM" Length="80" DataType="nvarchar" />
          <Column Name="PACK_DESC" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
   <Table Name="Corporation">
        <Files>
          <File FileName="Corporation.txt" NumberOfRows="5" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="CORP_ID" Length="30" DataType="nvarchar" IsPrimaryKey="true" />
          <Column Name="CORP_SHRT_NM" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
    </Tables>
  </Database>
</Order>
'
;WITH XMLNAMESPACES (DEFAULT 'XMLSchema') -- Define Default XML schema (i.e. xmlns )
SELECT 
 FileName  = files.x.value('@FileName','varchar(200)'),
 TableName  = tabs.x.value('@Name','varchar(200)'),
 ColumnName  = cols.x.value('@Name','varchar(200)'),
 ColumnDataType = cols.x.value('@DataType','varchar(200)'),
 ColumnLength = cols.x.value('@Length','varchar(200)')
FROM
     @MyXML.nodes('/Order[1]/Database[1]/Tables[1]/Table') tabs(x) 
CROSS APPLY tabs.x.nodes('Columns[1]/Column') cols(x) -- Loop through Columns level
CROSS APPLY tabs.x.nodes('Files[1]/File') files(x) -- Loop through Files Level

Result


Reference

No comments:

Post a Comment