Sql Server Xml Creation Vs Read Speed
By: | Updated: 2022-02-24 | Comments (83) | Related: 1 | 2 | 3 | four | More > XML
Trouble
In this article we wait at how to load an XML file into a SQL Server table and then how to query the XML data with several query examples.
Solution
At that place are different means to reach this chore of importing data from an XML file into a SQL Server table, but I am going to demonstrate i of easiest ways to accomplish this task.
These are the steps I performed for importing information into SQL Server and then parsing the XML into a relational format.
- Import XML information from an XML file into SQL Server tabular array using the OPENROWSET office
- Parse the XML data using the OPENXML function
Importing XML data from XML file using OPENROWSET
I accept an XML file downloaded from my FTP location to a local folder and information in this XML file looks like below. You can download the sample data here.
At present in order to import data from the XML file to a table in SQL Server, I am using the OPENROWSET role as you lot can come across below.
In the script beneath, I am first creating a table with a column of data blazon XML and so reading the XML data from the file using the OPENROWSET function by specifying the file location and name of the XML file equally you can see below:
CREATE DATABASE OPENXMLTesting Become USE OPENXMLTesting Go CREATE TABLE XMLwithOpenXML ( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME ) INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(Bulk 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS 10; SELECT * FROM XMLwithOpenXML
When I query the tabular array in which I accept imported the XML data, information technology looks like this. The XMLData column is an XML data blazon, it volition output a hyperlink every bit shown below:
Clicking on the hyperlink, in the above image, volition open another tab within SSMS with the XML data displayed as shown below.
Procedure XML information using OPENXML role
Now every bit I said before, XML data stored in a column of information type XML can be processed either by using XML functions available in SQL Server or past using the sp_xml_preparedocument stored process along with the OPENXML role.
Nosotros volition first call the sp_xml_preparedocument stored process by specifying the XML data which volition 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.
Annotation: the sp_xml_preparedocument stored procedure stores the XML information in SQL Server's internal cache, information technology is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. We should phone call the sp_xml_removedocument stored procedure as early possible, so that internal cache tin exist freed for other usage.
USE OPENXMLTesting GO DECLARE @XML Every bit 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](50) '@CustomerID', CustomerName [varchar](100) '@CustomerName', Address [varchar](100) 'Address' ) EXEC sp_xml_removedocument @hDoc Get
From the above XML data, I want to retrieve all the client information, and then I am navigating to the Customer element and querying CustomerID and CustomerName (please note the use of "@" before the name of the attribute) attributes and Address element in the above SELECT statement using the OPENXML function.
The structure of the resultset can be adamant with the "WITH" clause as shown above.
From the in a higher place XML data, I now want to retrieve all the customer data forth with OrderID and OrderDate placed past each individual customer and hence I am navigating to the Order element and then querying OrderID and OrderDate attributes.
If we want to navigate back to the parent or m parent level and become data from there, we need to utilise "../" to read the parent'due south data and "../../" to read the g parent's data 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, Accost, OrderID, OrderDate FROM OPENXML(@hDoc, 'ROOT/Customers/Client/Orders/Order') WITH ( CustomerID [varchar](50) '../../@CustomerID', CustomerName [varchar](100) '../../@CustomerName', Address [varchar](100) '../../Accost', OrderID [varchar](thousand) '@OrderID', OrderDate datetime '@OrderDate' ) EXEC sp_xml_removedocument @hDoc GO
The result of the higher up query can be seen in the image beneath. You tin see below all the customers and all the orders placed by each customer.
At present let'south go ane level deeper. This time from the above XML data, I desire to think all the customer data and their orders along with ProductID and Quantity from each gild placed. And hence, as you lot can see below I am navigating to the OrderDetail and retrieving the ProductID and Quantity attributes' values. At the same fourth dimension I am using "../" to accomplish the parent level to become Order information available at the parent level whereas I am using "../../../" to reach to the slap-up grand parent level to grab Customer information as shown beneath:
USE OPENXMLTesting GO DECLARE @XML Every bit XML, @hDoc Every bit INT, @SQL NVARCHAR (MAX) SELECT @XML = XMLData FROM XMLwithOpenXML EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML SELECT CustomerID, CustomerName, Accost, OrderID, OrderDate, ProductID, Quantity FROM OPENXML(@hDoc, 'ROOT/Customers/Client/Orders/Order/OrderDetail') WITH ( CustomerID [varchar](fifty) '../../../@CustomerID', CustomerName [varchar](100) '../../../@CustomerName', Address [varchar](100) '../../../Address', OrderID [varchar](one thousand) '../@OrderID', OrderDate datetime '../@OrderDate', ProductID [varchar](50) '@ProductID', Quantity int '@Quantity' ) EXEC sp_xml_removedocument @hDoc Get
The result of the in a higher place query can be seen in the epitome below. You tin can see all the customer data and their orders forth with ProductID and Quantity from each order placed.
Next Steps
- Review these related tips
- Replacing OPENXML with the XML nodes() Function in SQL Server
Related Articles
Popular Articles
About the author
Arshad Ali is a SQL and BI Developer focusing on Information Warehousing projects for Microsoft.
View all my tips
Article Final Updated: 2022-02-24
Source: https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
0 Response to "Sql Server Xml Creation Vs Read Speed"
Postar um comentário