How to read XML in an SQL Server

This post explains how to read an XML document using SQL queries.

How to read an XML file using SQL

 

The goal of this post is to read XML data from an XML file. We will create respective tables accordingly and obtain some sort of meaningful information. It goes over the steps we need to perform in SQL in order to complete this.

Goals

1. Read an XML file and save it in a table. (For a clearer approach, we are creating a separate table for the raw XML)
2. Fetch the XML field data from a raw XML table.
3. Create table(s) according to XML data.

Instructions 

 

Read the XML file using OPENROWSET, and save the data in the table.

 

In the below code, we create a table for our raw XML data. Then read the XML file and save the same data into the "PointsXML" table.

CREATE TABLE PointsXML  -- Create table for raw XML file.
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)


INSERT INTO PointsXML(XMLData, LoadedDateTime)  -- Insert xml data into table.
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\PointsXML.xml', SINGLE_BLOB) AS x;  -- Read data from XML file.


SELECT * FROM PointsXML

Create a table from the saved XML data.

DECLARE @XMLDoc AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XMLDoc = XMLData FROM dbo.PointsXML

EXEC sp_xml_preparedocument  @hDoc OUTPUT, @XMLDoc
SELECT *
FROM OPENXML(@hDoc, '/PointsImport/Points/Point')
WITH
(
id [int],
name [varchar](50),
displayName [varchar](200),
type [varchar](30),
X [int],
Y [int],
isPublic [varchar](5),
tariffZoneId [int]
)
EXEC sp_xml_removedocument @hDoc

Thanks for reading! Happy Coding :)