Post
Ask Question

How to read XML in an SQL Server

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.
  1. CREATE TABLE PointsXML  -- Create table for raw XML file.
  2. (  
  3. Id INT IDENTITY PRIMARY KEY,  
  4. XMLData XML,  
  5. LoadedDateTime DATETIME  
  6. )  
  7.   
  8.   
  9. INSERT INTO PointsXML(XMLData, LoadedDateTime)  -- Insert xml data into table.
  10. SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()   
  11. FROM OPENROWSET(BULK 'C:\PointsXML.xml', SINGLE_BLOB) AS x;  -- Read data from XML file.
  12.   
  13.   
  14. SELECT * FROM PointsXML 
Create a table from the saved XML data.
  1. DECLARE @XMLDoc AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)  
  2.   
  3. SELECT @XMLDoc = XMLData FROM dbo.PointsXML  
  4.   
  5. EXEC sp_xml_preparedocument  @hDoc OUTPUT, @XMLDoc  
  6. SELECT *  
  7. FROM OPENXML(@hDoc, '/PointsImport/Points/Point')  
  8. WITH   
  9. (  
  10. id [int],  
  11. name [varchar](50),  
  12. displayName [varchar](200),  
  13. type [varchar](30),  
  14. X [int],  
  15. Y [int],  
  16. isPublic [varchar](5),  
  17. tariffZoneId [int]  
  18. )  
  19. EXEC sp_xml_removedocument @hDoc  
Thanks for reading! Happy Coding :)