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

https://www.simple-talk.com/sql/database-administration/manipulating-xml-data-in-sql-server/

https://msdn.microsoft.com/en-GB/library/ms191184.aspx