How To Add New Rows In Xml Using Notepad++

By: | Updated: 2022-02-24 | Comments (83) | Related: 1 | 2 | 3 | 4 | More > XML

Problem

In this article nosotros expect at how to load an XML file into a SQL Server table and then how to query the XML information with several query examples.

Solution

There are unlike ways to achieve this chore of importing data from an XML file into a SQL Server table, but I am going to demonstrate one of easiest ways to accomplish this task.

These are the steps I performed for importing information into SQL Server then parsing the XML into a relational format.

Import XML data from an XML file into SQL Server table using the OPENROWSET function
Parse the XML information using the OPENXML function

Importing XML data from XML file using OPENROWSET

I have an XML file downloaded from my FTP location to a local binder and data in this XML file looks like below. You tin download the sample data here.

Importing XML data from XML file using OPENROWSET

Now in guild to import information from the XML file to a table in SQL Server, I am using the OPENROWSET function as yous can encounter below.

In the script below, I am first creating a table with a column of data type XML so reading the XML data from the file using the OPENROWSET part past specifying the file location and proper name of the XML file every bit you can see below:

CREATE DATABASE OPENXMLTesting Go  USE OPENXMLTesting GO  CREATE Tabular array XMLwithOpenXML ( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME )  INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()  FROM OPENROWSET(Majority 'D:\OpenXMLTesting.xml', SINGLE_BLOB) As x;  SELECT * FROM XMLwithOpenXML

When I query the table in which I have imported the XML data, it looks like this. The XMLData column is an XML data type, it will output a hyperlink as shown below:

As XMLData column is of XML data type, it will give an hyperlink

Clicking on the hyperlink, in the above image, will open another tab within SSMS with the XML data displayed as shown below.

xml data in SQL Server

Process XML data using OPENXML part

Now as I said before, XML data stored in a cavalcade of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.

We will commencement call the sp_xml_preparedocument stored procedure by specifying the XML data which will then output the handle of the XML data that it has prepared and stored in internal cache.

Then we will use the handle returned past the sp_xml_preparedocument stored procedure in the OPENXML function to open up the XML data and read it.

Note: the sp_xml_preparedocument stored procedure stores the XML data in SQL Server's internal cache, it is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. Nosotros should call the sp_xml_removedocument stored procedure as early possible, so that internal enshroud tin be freed for other usage.

Apply OPENXMLTesting GO  DECLARE @XML As XML, @hDoc AS INT, @SQL NVARCHAR (MAX)  SELECT @XML = XMLData FROM XMLwithOpenXML  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML  SELECT CustomerID, CustomerName, Address FROM OPENXML(@hDoc, 'ROOT/Customers/Customer') WITH  ( CustomerID [varchar](fifty) '@CustomerID', CustomerName [varchar](100) '@CustomerName', Accost [varchar](100) 'Address' )  EXEC sp_xml_removedocument @hDoc GO

From the to a higher place XML data, I desire to retrieve all the client information, and so I am navigating to the Client chemical element and querying CustomerID and CustomerName (please annotation the use of "@" before the name of the attribute) attributes and Address element in the in a higher place SELECT statement using the OPENXML function.

The structure of the resultset can exist determined with the "WITH" clause as shown above.

Process XML data using OPENXML function

From the in a higher place XML data, I now want to remember all the client information along with OrderID and OrderDate placed past each individual customer and hence I am navigating to the Guild element and then querying OrderID and OrderDate attributes.

If we want to navigate back to the parent or grand parent level and get data from there, we need to utilize "../" to read the parent's information and "../../" to read the grand parent's information and then on.

USE OPENXMLTesting Go  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)  SELECT @XML = XMLData FROM XMLwithOpenXML  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML  SELECT CustomerID, CustomerName, Address, OrderID, OrderDate FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Gild') WITH  ( CustomerID [varchar](fifty) '../../@CustomerID', CustomerName [varchar](100) '../../@CustomerName', Address [varchar](100) '../../Address', OrderID [varchar](1000) '@OrderID', OrderDate datetime '@OrderDate' )  EXEC sp_xml_removedocument @hDoc GO

The issue of the above query can exist seen in the image below. You can meet below all the customers and all the orders placed by each client.

querying CustomerID and CustomerName

Now allow's go i level deeper. This time from the above XML information, I desire to recall all the customer information and their orders forth with ProductID and Quantity from each club placed. And hence, every bit you can see below I am navigating to the OrderDetail and retrieving the ProductID and Quantity attributes' values. At the same time I am using "../" to reach the parent level to get Order data available at the parent level whereas I am using "../../../" to achieve to the bang-up grand parent level to grab Customer information as shown below:

Use OPENXMLTesting GO  DECLARE @XML AS XML, @hDoc Every bit INT, @SQL NVARCHAR (MAX)  SELECT @XML = XMLData FROM XMLwithOpenXML  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML  SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail') WITH  ( CustomerID [varchar](50) '../../../@CustomerID', CustomerName [varchar](100) '../../../@CustomerName', Accost [varchar](100) '../../../Address', OrderID [varchar](1000) '../@OrderID', OrderDate datetime '../@OrderDate', ProductID [varchar](50) '@ProductID', Quantity int '@Quantity' )  EXEC sp_xml_removedocument @hDoc GO

The result of the above query can be seen in the image below. You can see all the customer information and their orders forth with ProductID and Quantity from each order placed.

The result of the above query