Importing and Processing data from XML files into SQL Server tables

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


Problem

In this article we look 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

There are different ways to achieve this task 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 data into SQL Server and 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 data 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 folder and data in this XML file looks like below. You can download the sample data here.

Importing XML data from XML file using OPENROWSET

Now in order to import data from the XML file to a table in SQL Server, I am using the OPENROWSET function as you can see below.

In the script below, I am first creating a table with a column of data type XML and then reading the XML data from the file using the OPENROWSET function by specifying the file location and name of the XML file as you can see below: 

CREATE DATABASE OPENXMLTesting
GO

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 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 function

Now as I said before, XML data stored in a column 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 first 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 by the sp_xml_preparedocument stored procedure in the OPENXML function to open 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. We should call the sp_xml_removedocument stored procedure as early possible, so that internal cache can be freed for other usage. 

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
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH 
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)

EXEC sp_xml_removedocument @hDoc
GO

From the above XML data, I want to retrieve all the customer information, so 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 determined with the "WITH" clause as shown above.

Process XML data using OPENXML function

From the above XML data, I now want to retrieve all the customer information along with OrderID and OrderDate placed by 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 grand parent level and get data from there, we need to use "../" to read the parent's data and "../../" to read the grand parent's data and so 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/Order')
WITH 
(
CustomerID [varchar](50) '../../@CustomerID',
CustomerName [varchar](100) '../../@CustomerName',
Address [varchar](100) '../../Address',
OrderID [varchar](1000) '@OrderID',
OrderDate datetime '@OrderDate'
)

EXEC sp_xml_removedocument @hDoc
GO

The result of the above query can be seen in the image below. You can see below all the customers and all the orders placed by each customer.

querying CustomerID and CustomerName

Now let's go one level deeper. This time from the above XML data, I want to retrieve all the customer information and their orders along with ProductID and Quantity from each order placed. And hence, as 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 information available at the parent level whereas I am using "../../../" to reach to the great grand parent level to grab Customer information as shown below:

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, ProductID, Quantity
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH 
(
CustomerID [varchar](50) '../../../@CustomerID',
CustomerName [varchar](100) '../../../@CustomerName',
Address [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 along with ProductID and Quantity from each order placed.

The result of the above query
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-02-24

Comments For This Article




Thursday, March 21, 2024 - 12:08:00 PM - Elisabeth Buxbaum Back To Top (92101)
Hi there, thanks for this tip. It works fine with the xml you posted as sample.
However, I have xml that it just will not work with. It gives me the rows, but no values.
Is there any chance you could have a look at the xml and help?

Monday, December 26, 2022 - 9:49:26 PM - Mehran Back To Top (90784)
Dear Arshad or other friends , could you help to extract this XML RAML formats since (RAML and Header lines ) prevent from true extraction of the values, what is the true syntax in this regard?
----------------------------------------
DECLARE @docHandle int;
DECLARE @XmlDocument XML;
SET @XmlDocument = '
<raml xmlns="raml20.xsd" version="2.0">
<cmData type="actual">
<header>
<log dateTime="2022-11-21T09:34:57" action="created" appInfo="ActualExporter">InternalValues are used</log>
</header>
<managedObject class="MRBTS" version="SBTS17A_1707_001" distName="PLMN-PLMN/MRBTS-840009" id="14512301">
<p name="name">MRBTS-ILG0009</p>
<p name="btsName">ILG0009</p>
</managedObject>
</cmData>
</raml>
';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
SELECT *
FROM OPENXML (@docHandle, '/raml/cmData/header/log/managedObject/p')
WITH ( Parameters varchar(50) '@name',
Value varchar(50) '.'
);
EXEC sp_xml_removedocument @docHandle;
----------------------------------------
Thanks Indeed

Thursday, October 20, 2022 - 10:04:29 PM - Jay Myrick Back To Top (90621)
I have an xml document that has a inline schema.

?xml version="1.0" encoding="utf-8"?>
<RS xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:complexType name="R">
<xsd:sequence>
<xsd:element name="C0" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula=""ADHOC Analytics"."Soldier Attributes"."Employee ID (EMPLID)"" saw-sql:displayFormula=""Soldier Attributes"."Employee ID (EMPLID)"" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Soldier Attributes" saw-sql:columnHeading="Employee ID (EMPLID)" saw-sql:isDoubleColumn="false" saw-sql:columnID="c23a70d28a923ef93" saw-sql:length="11" saw-sql:scale="0" saw-sql:precision="11"/>

</xsd:sequence>
</xsd:complexType>
</xsd:schema>

Is there a way to read the schema and get the attributes from it?

The reason is that is that it is possible that the element C0 might not be EmployeeID as in the example. However, the next time the adhoc query creates the report EmployeeID could be C1 and C0 be another value.

So I want to be able to test that my data is the correct element and if not, I need to adjust.

Thanks for any help.

Jay

Monday, February 14, 2022 - 1:35:25 PM - tal Back To Top (89788)
you'r the best , thank you so very much.

Monday, February 14, 2022 - 12:13:55 PM - Greg Robidoux Back To Top (89787)
Hi Tal.

try this:

declare @xml as xml

set @xml='
<Details>
<StoreData>
<StoreCode>1234</StoreCode>
<Lines>
<ItemBarcode>abcd</ItemBarcode>
<ItemQty>1</ItemQty>
<Qty>box</Qty>
</Lines>
</StoreData>
<StoreData>
<StoreCode>987</StoreCode>
<Lines>
<ItemBarcode>djhdufgre</ItemBarcode>
<ItemQty>2</ItemQty>
<Qty>pack</Qty>
</Lines>
</StoreData>
</Details>'

--this is my query:

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

--SELECT @XML = XmlCol FROM T

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT StoreCode, itemqty
FROM OPENXML(@hDoc, 'Details/StoreData/Lines')
WITH
(
StoreCode [varchar](150) '../StoreCode',
itemqty [varchar](100) 'ItemQty'
)

EXEC sp_xml_removedocument @hDoc
GO

Monday, February 14, 2022 - 10:37:49 AM - tal Back To Top (89786)
Hello,
pleas can you help me, I am trying to extract the data from the xml like in your example but i only get the StoreCode. can you pleas advise why?

this is my xml:

<Details>
<StoreData>
<StoreCode>1234</StoreCode>
<Lines>
<ItemBarcode>abcd</ItemBarcode>
<ItemQty>1</ItemQty>
<Qty>box</Qty>
</Lines>
</StoreData>
<StoreData>
<StoreCode>987</StoreCode>
<Lines>
<ItemBarcode>djhdufgre</ItemBarcode>
<ItemQty>2</ItemQty>
<Qty>pack</Qty>
</Lines>
</StoreData>
</Details>

this is my query:

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

SELECT @XML = XmlCol FROM T

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT StoreCode, itemqty
FROM OPENXML(@hDoc, 'Details/StoreData')
WITH
(
StoreCode [varchar](150) 'StoreCode',
itemqty [varchar](100) 'itemqty'
)

EXEC sp_xml_removedocument @hDoc
GO

the result:
1234 NULL
987 NULL

Tuesday, November 16, 2021 - 3:56:35 PM - CarlosDaniel Back To Top (89453)
Hello.

I have a similar problem, I have several XML files in a folder, but when reading these, I only read the last file I have, be it 2, 5 or more. It will always read the latest file.

This is my Query.

CREATE TABLE dbo.XMLFilesTable
--(
--Id INT IDENTITY PRIMARY KEY,
--FileName VARCHAR(100),
--XMLData XML,
--LoadedDateTime DATETIME
--)

select * from dbo.XMLFilesTable
--delete from dbo.XMLFilesTable


------------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList


--Folder path where files are present
Declare @SourceFolder VARCHAR(100)
SET @SourceFolder='C:\XML_TEST\'

CREATE TABLE #FileList (
Id int identity(1,1),
FileName nvarchar(255),
Depth smallint,
FileFlag bit)

--Load the file names from a folder to a table
INSERT INTO #FileList (FileName,Depth,FileFlag)
EXEC xp_dirtree @SourceFolder, 10, 1

--Use Cursor to loop throught files
--Select * From #FileList
Declare @FileName VARCHAR(500)

DECLARE Cur CURSOR FOR
SELECT FileName from #FileList
where fileflag=1

OPEN Cur
FETCH Next FROM Cur INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @InsertSQL NVARCHAR(MAX)=NULL
--Prepare SQL Statement for insert
SET @InsertSQL=
'INSERT INTO dbo.XMLFilesTable(FileName, LoadedDateTime,XMLData)
SELECT '''+@FileName+''',getdate(),Convert(XML,BulkColumn ) As BulkColumn
FROM Openrowset( Bulk '''+@SourceFolder+@FileName+''', Single_Blob) as Image'


--Print and Execute SQL Insert Statement to load file
Print @InsertSQL
EXEC(@InsertSQL)

FETCH Next FROM Cur INTO @FileName
END
CLOSE Cur
DEALLOCATE Cur

GO

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


SELECT @XML = XMLData FROM dbo.XMLFilesTable


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT INTO MasterData
SELECT entityType,purchaseOrganization,uniqueCreatorIdentification,gln,name,requestedDeliveryDate,CancelDate,gtin,requestedQuantity
FROM OPENXML(@hDoc, 'Orders/lineItem/tradeItemIdentification')
WITH
(
entityType varchar(5) '../../orderIdentification/entityType',
purchaseOrganization varchar(5) '../../orderIdentification/purchaseOrganization',
uniqueCreatorIdentification varchar(20) '../../orderIdentification/uniqueCreatorIdentification',
gln [varchar](10) '../../shipTo/gln',
name [varchar](60) '../../shipTo/nameAndAddress/name',
requestedDeliveryDate datetime '../../orderLogisticalDateGroup/orderDeliveryInformation/requestedDeliveryDate',
CancelDate datetime '../../orderLogisticalDateGroup/OrderCancelInformation/CancelDate',
gtin varchar(13) 'gtin',
requestedQuantity int '../requestedQuantity'
)
WHERE NOT EXISTS (select gtin from MasterData);

select * from [dbo].[MasterData]

EXEC sp_xml_removedocument @hDoc
GO
-------------------------------------------

Thursday, September 10, 2020 - 5:05:16 AM - prasad Back To Top (86449)
return only 1st value for all row while retrieving element text value from xml using open xml file.

Tuesday, April 28, 2020 - 9:25:38 PM - Kashyap Amin Back To Top (85494)

How would I take only one column from a table in SQL server which has XML string and convert that in to a new table in a database that will have the field names and data from this column

Wednesday, March 11, 2020 - 12:20:54 AM - John Back To Top (85050)

I have used your example to.import large files quickly into my SQL dB and am able to query the single entry and extract the data I require. (Thanks by the way for the tutorial), however whilst trying to speed up my query and optimise I posted my solution on another forum and was told I should be using xquery instead of openxml as it was outdated.  Can you give a reason why? Openxml works fine for what I am trying to do but I would always like to use the preferred way of doing things

Should I rewrite my queries? Or is openxml good enough for the job?

Sorry for the general question, I just want my code to be as best as it can be.

Thanks for your time.

Sunday, February 23, 2020 - 4:02:11 AM - shima jalaeian Back To Top (84739)

Thank you so much

Wednesday, September 11, 2019 - 2:08:48 AM - Arpan Back To Top (82349)

Getting the following error while trying to execute the query.

Cannot bulk load because the file "D:\ABC.xml" could not be opened. Operating system error code 21(The device is not ready.).

Thursday, June 27, 2019 - 11:13:40 AM - Greg Robidoux Back To Top (81609)

Hi Fen,

You could also try the Import Wizard in SSMS.

https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

You could look at these options too, but you might have the same permissions issues.

https://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/

-Greg

Wednesday, June 19, 2019 - 4:31:25 AM - Fen Back To Top (81515)

Hi Arshard,

Thank you for your sharing! It is definitely nice ans useful. However, when I try your method, I found out I don't have the permission to use bulk load. Therefore, do you also know other method to insert xml data to SQL tables? It would be very nice if you know something.

Looking forward to hearing from you and thank you in advance!

Thursday, December 6, 2018 - 4:15:07 AM - Zazzy Back To Top (78410)

Hi, your description is wonderful; I have just a problem. I would like to insert the result in a local table.

1) I have create a table:


use OPENXMLTesting

CREATE TABLE Test (
CustomerID [varchar](50),
CustomerName [varchar](100),
Address [varchar](100),
OrderID [varchar](1000),
OrderDate datetime,
ProductID [varchar],
Quantity int )

2) Here I have tried to insert --> 

Msg 8152, Level 16, State 14, Line 12
String or binary data would be truncated.
The statement has been terminated.

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


--Insert Into OPENXMLTesting.dbo.Test
--(
--[CustomerID]
--,[CustomerName]
--,[Address]
--,[OrderID]
--,[OrderDate]
--,[ProductID]
--,[Quantity]
--)

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',
Address [varchar](100) '../../../Address',
OrderID [varchar](1000) '../@OrderID',
OrderDate datetime '../@OrderDate',
ProductID [varchar](50) '@ProductID',
Quantity int '@Quantity'
)



EXEC sp_xml_removedocument @hDoc
GO

Tuesday, November 20, 2018 - 4:30:13 PM - marco Back To Top (78299)

 Hello i would like to know how to import those query results into a new table inside that database? is that possible?

Monday, October 15, 2018 - 11:42:34 AM - Sebastian Back To Top (77953)

Hi,

I am trying to use  OPENXML to retrieve data from XML but I got problem when I have two XMLNS inside the code. The code below works well if I remove the xmlns inside <BIK REQUEST>. But If left it as originally is then I got empty values. Can you help me please resolve the problem ?

 

DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


SET @Xml = N'
<SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
 <BIK_REQUEST xmlns="">
    <siBikNetResponse>
        <consentDate>2018-07-29</consentDate>
        <citizenshipStatus>citizen</citizenshipStatus>
        <nationality>PL</nationality>
        <pesel>98070902702</pesel>
    </siBikNetResponse>
 </BIK_REQUEST>
</SiBikNet>';


EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

SELECT *
FROM
       OPENXML(@Handle,'/t:SiBikNet/t:BIK_REQUEST/t:siBikNetResponse', 2)
           WITH (   nationality NVARCHAR(10) 't:nationality',
                    pesel  NVARCHAR(10)      't:pesel '
                );

EXEC sys.sp_xml_removedocument @Handle;

 

 

Thursday, June 7, 2018 - 5:52:41 AM - Uli Bethke Back To Top (76143)

This is a good explanation how to use SQL Server native libraries to convert XML to a relational format and vice versa. It definitely works for simple XML. However, this manual approach can be quite cumbersome when dealing with complex XML. I am talking about industry data standards such as HL7, FpML, ACORD, FIXML, NDC etc. etc. The specs are typically a few thousand pages long. If you have large volumes of XMLs and a streaming requirement things don't get easier. The same is true for frequently changing XML files or where you don't have an XSD. I have written up a blog post that looks into all these items and automatically converts complex FIXML to MS SQL Server https://sonra.io/2018/06/05/converting-fixml-to-sql-server/

Wednesday, May 23, 2018 - 8:29:47 AM - Balazs Takacs Back To Top (76009)

@Simon Mallett

The solution for your problem is to write 'text()' instead of '../OrderDetail' , like so:

SELECT OrderDetail
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH
(
OrderDetail [varchar](50) 'text()'
)


EXEC sp_xml_removedocument @hDoc.

Thursday, April 12, 2018 - 4:30:58 AM - Gaurav Back To Top (75682)

 Hi Arshad,

 

Thanks for the tip, its really helpful. However, I am still facing some issues. I used the exact code as you have given above, but for some reason I dont get any data in my select statement. The first part works fine where it selects the entire XML into 1 column, but the second part doesnt give out any data. 

 

It creates a nice empty table with all the columns as specified in my select statement.

 

USE FALK_EDW

GO

 

 

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

 

 

SELECT @XML = XMLData FROM XMLwithOpenXML

 

 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 

 

SELECT ID, Title, Label,AteaRapidProsjektnummer,Kontraktsanvarlig_x0020_SkanskaId,Kontraktsnummer,Avtalepart_x0020_land,

ISI_SO_Leverand_x00f8_rOrgNr, Avtalepart,Kontraktsansvarlig_x0020_avtalepart,Kontraktsdato,Kontrakt_x0020_varighet_x0020_fra,

Kontrakt_x0020_varighet_x0020_til,Valuta,Kontraktssum_kontraktsarkiv,Kontraktsdokumenttype,Garanti_x0020_fra,Garanti_x0020_til

FROM OPENXML(@hDoc, 'feed/entry/author/content/mproperties/ISIFirmakode')

WITH 

(

ID [int] '../@ID',

Title [varchar](50) '../@Title',

Label [int] '@Label',

AteaRapidProsjektnummer [varchar](50) '../@AteaRapidProsjektnummer',

Kontraktsanvarlig_x0020_SkanskaId [varchar](50) '../@Kontraktsanvarlig_x0020_SkanskaId',

Kontraktsnummer [varchar](100) '../@Kontraktsnummer',

Avtalepart_x0020_land [varchar](50) '../@Avtalepart_x0020_land',

ISI_SO_Leverand_x00f8_rOrgNr [varchar](50) '../@ISI_SO_Leverand_x00f8_rOrgNr',

Avtalepart [varchar](100) '../@Avtalepart',

Kontraktsansvarlig_x0020_avtalepart [varchar](100) '../@Kontraktsansvarlig_x0020_avtalepart',

Kontraktsdato[int] '../@Kontraktsdato',

Kontrakt_x0020_varighet_x0020_fra[int] '../@Kontrakt_x0020_varighet_x0020_fra',

Kontrakt_x0020_varighet_x0020_til[int] '../@Kontrakt_x0020_varighet_x0020_til',

Valuta[varchar](50) '../@Valuta',

Kontraktssum_kontraktsarkiv[int] '../@Kontraktssum_kontraktsarkiv',

Kontraktsdokumenttype[varchar](100) '../@Kontraktsdokumenttype',

Garanti_x0020_fra[int] '../@Garanti_x0020_fra',

Garanti_x0020_til[int] '../@Garanti_x0020_til'

)

 

 

EXEC sp_xml_removedocument @hDoc

GO

 

Friday, April 6, 2018 - 12:34:11 PM - Simon Mallett Back To Top (75630)

 Hi, This has been immensely helpful and I am at last well on the way to automating a Met Office XML download and store on SQLServer for wind calculations.  But... On the met office download the XML is structured  at the bottom level like this:

<Period type="Day" value="2018-04-04Z">       
<Rep xxD="S" F="6" G="34" H="80" Pp="89" S="20" T="9" V="GO" W="12" U="2">540</Rep>
<Rep xxD="S" F="5" G="40" H="86" Pp="85" S="25" T="9" V="GO" W="15" U="3">720</Rep>

Note the 540 and 720.  I have modified the sample that is used on this page:

<ROOT>

  <Customers>
    <Customer CustomerID="C001" CustomerName="Arshad Ali">
      <Orders>
        <Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
          <OrderDetail ProductID="10" Quantity="5">127</OrderDetail> 
          <OrderDetail ProductID="11" Quantity="12">125</OrderDetail>
          <OrderDetail ProductID="42" Quantity="10">126</OrderDetail>
        </Order>     
     </Orders>
   <Address> Address line 1, 2, 3</Address>
 </Customer>
</Customers>
</ROOT>

Using the sample and adding :  OrderDetail int '../OrderDetail'  While everything else still works fine , OrderDetail comes out as 127 on all three rows.

As the data I am using is downloaded from a stock data source, I cannot change the original xml, any thoughts on how to correctly handle this.  The "Address" field is similar but seems to work fine. 

 

Thursday, April 5, 2018 - 12:37:55 AM - Anil panwar Back To Top (75612)

 Hi Arshad,

 

I have resolved previous artical issue.

 

On question How can i get the value of prallel Node value from XML  in one select 

 

Like 

<ROOT>
<Customers>
<Customer CustomerID="C001" CustomerName="Arshad Ali">
<Orders>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail ProductID="10" Quantity="5" />
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
<OrderTemp1>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail Categoryid="10" Quantity="5" />
<OrderDetail Categoryid="11" Quantity="12" />
<OrderDetail CategoryID="42" Quantity="10" />
</OrderTemp1>
<OrderTemp>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail Typeid="10" Quantity="5" />
<OrderDetail TypeId="11" Quantity="12" />
<OrderDetail TypeId="42" Quantity="10" />
</OrderTemp>
</Orders>
 
<Address> Address line 1, 2, 3</Address> </Customer> <Customer CustomerID="C002" CustomerName="Paul Henriot"> <Orders> <Order OrderID="10245" OrderDate="2011-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12" /> <OrderDetail ProductID="42" Quantity="10" /> </Order> </Orders> <Address> Address line 5, 6, 7</Address> </Customer> <Customer CustomerID="C003" CustomerName="Carlos Gonzlez"> <Orders> <Order OrderID="10283" OrderDate="2012-08-16T00:00:00"> <OrderDetail ProductID="72" Quantity="3" /> </Order> </Orders> <Address> Address line 1, 4, 5</Address> </Customer> </Customers> </ROOT>

 

 

 

How i will get the Category id and TYpe id also in one query 

 

Like you have used above ../../ for fetching the value of parent node, so how can i get the value of siblink node.

 

Please help 

 

Anil Panwar

 

 

 

Wednesday, March 28, 2018 - 12:52:14 AM - Anil panwar Back To Top (75541)

Hi Arshad , 

Thanks for writing so helpfull article. I need one help. I want to Insert values selected from XML in another table i have done in Following manner.

 

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

 

SELECT @XML = XMLData FROM XOX_Meps_XMLwithOpenXML_TB Where id=2

 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 

 

Insert into dbo.MAE_MepsAssignmentEvent_TB(

CLI_Id,

MAE_EventId,

MTR_Id,

MAE_AssignmentId,

MAE_AssignmentName,

MAE_OrganizationId,

MAE_OrganizationName,

MAE_UserName,

MAE_CreatedDateTime,

MAE_Comment,

MAE_DecisionType

) 

SELECT CLI_Id,MEV_EventId,MTR_Id, MAE_AssignmentId, MAE_AssignmentName,MAE_OrganizationId,MAE_OrganizationName,

MAE_UserName,MAE_CreatedDateTime,MAE_Comment,MAE_DecisionType

FROM OPENXML(@hDoc, 'AssignmentEvent')

WITH 

(

CLI_Id Int 'EventId' ,

MEV_EventId varchar (200) 'EventId',

MTR_Id int '@P_MTT_Id',

MAE_AssignmentId varchar (200) 'AssignmentId',

MAE_AssignmentName varchar (200) 'AssignmentName',

MAE_OrganizationId varchar (200) 'OrganizationId',

MAE_OrganizationName varchar (200) 'OrganizationName',

MAE_UserName varchar (200) 'UserName',

MAE_CreatedDateTime datetime 'CreatedDateTime',

MAE_Comment varchar (200) 'Comment',

MAE_DecisionType int 'DecisionType'

 

)

 

There is a field MTR_Id int '@P_MTT_Id', Which is forigen key here which is returned by a another SP as OUT parameter. 

So how can i Insert that variable value here.

1- If i am taking in above manner then it is inserting null value in that column.

2- If i am taking it in this manner than MTR_Id int  @P_MTT_Id, than getting XML parsing error.

 

Please help on this.

 

 

Tuesday, March 27, 2018 - 3:42:51 PM - Dan Back To Top (75533)

 Hi,

I was able to use your example to create and load table XMLwithOpenXML from an XML file that I have.

When I query the XMLwithOpenXML table I do get a web link and clicking on the web link does open a 2nd tab which displays the XML data.

When I try to query the table with the following script I see the ID ExternalID and LongName column headings but there is no data

----------------------------------------------------------------------------
USE [TRIUMPH]
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT Id, ExternalId, LongName
FROM OPENXML(@hDoc, 'Data/Entities/Entity')
WITH
(
Id Int '@Id',
ExternalId [varchar](100) '@ExternalId',
LongName [varchar](100) '@LongName'
)

EXEC sp_xml_removedocument @hDoc
GO

-------------------------------------------------------------------------

Can you see what I might be doing wrong?

Dan

Friday, March 16, 2018 - 11:12:59 AM - jerome Back To Top (75446)

 

 Tank you for this post, very helpful for me

Here is my implentation with a file name in variable InPathFile (you must build a sql string request)

DECLARE @Sql NVARCHAR(1000)

SET @Sql = N'INSERT INTO [exchange].[tmpImport_XMLwithOpenXML](XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK '''+@InPathFile+''', SINGLE_BLOB) AS x;'

EXEC (@Sql)

hope will help other guys

Thursday, February 22, 2018 - 12:13:11 PM - Muhammad Back To Top (75279)

Hi Dylan

I'd similar situation like yours and I developed a cursor on top of the query provided by Arshad as

 

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

DECLARE XMLData CURSOR FOR
SELECT XMLData FROM XMLwithOpenXML

OPEN XMLData
FETCH NEXT FROM XMLData INTO @XML

BEGIN....

Thursday, October 19, 2017 - 3:46:24 PM - Dylan Back To Top (68550)

 

I do exactly as your suggestion, but it pick up only first customer information and exclude the rest, I have 6 customers on my test file. What did I missing?

 

Wednesday, June 7, 2017 - 4:30:34 AM - Breuil Alexis Back To Top (56911)

Hello everyone,

I have a little problem with the code. I have an XML file with a lot of data, and i want to put all that data in in SQL SERVER database, in a table with columns like the last exemple of the article. So, here is my code :

 

USE [Seica-Takaya]

GO

 

 

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

 

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 

SELECT @XML = XMLData FROM XMLwithOpenXML

 

SELECT Judge,DTotal,Total ,MName ,DateOfTest ,TimeOfTest ,TestTime,MVersion ,TesterID ,MIndex ,MMessage FROM OPENXML(@hDoc, 'Faildata/Header')

WITH (

Judge [nvarchar](10) '@Judge', 

DTotal float 'DTotal', 

Total float 'Total',

MName [nvarchar](255) 'MName', 

DateOfTest datetime 'DateOfTest', 

TimeOfTest time 'TimeOfTest', 

TestTime time 'TestTime', 

MVersion [nvarchar](10) 'MVersion', 

TesterID [nvarchar](255) 'TesterID', 

MIndex [nvarchar](255) 'MIndex', 

MMessage [nvarchar](255) 'MMessage')

 

SELECT GroupID ,Step ,Aux ,Parts ,Val ,Comment ,Loc ,Func ,PTolUnit ,MTolUnit ,MMode ,MRange,MTime ,RefEL ,RefValUnit ,RefFigUnit ,TestEL ,TestVal ,TestFig ,AccessProbe ,Pin ,Contact ,DPass ,DFail ,TPass ,TFail FROM OPENXML(@hDoc, 'Faildata/ABSIDE/Group/Step/T.Info')

WITH ( 

GroupID int '@GroupID', 

Step [nvarchar](255) 'Step', 

Aux [varchar](10) 'Aux', 

Parts [nvarchar](10) 'Parts', 

Val [nvarchar](10) 'Val',

Comment [nvarchar](255) 'Comment',

Loc [nvarchar](10) 'Loc',

Func [nvarchar](10) 'Func',

PTolUnit float 'PTolUnit',

MTolUnit float 'MTolUnit',

MMode [nvarchar](255) 'MMode',

MRange int 'MRange',

MTime float 'MTime',

RefEL [varchar](10) 'RefEL',

RefValUnit float 'RefValUnit',

RefFigUnit float 'RefFigUnit',

TestEL [varchar](10) 'TestEL',

TestVal float 'TestVal',

TestFig float 'TestFig',

AccessProbe [varchar](255) 'AccessProbe',

Pin float 'Pin',

Contact float 'Contact',

DPass float 'DPass',

DFail float 'DFail',

TPass float 'TPass',

TFail float 'TFail'

)

 

 

EXEC sp_xml_removedocument @hDoc

GO

 

So, as you can expect, my table is create, the columns are create. But they are empty ! It's blank. And i don't know why there isn't any data in the columns they are relative to.

Thanks a lot !

 

Wednesday, March 15, 2017 - 6:45:43 PM - Simon Leijh Back To Top (51171)

 Life Saver!

Thanks for the great how-to. I've just used this to parse multiple large XML files into a SQL DB. From the steps above, I then used a SELECT ... INTO ... FROM OPENXML query to get the XML data into a table on a DB. Once you have the data in a table on a DB, the response rate to subsequent queries is drastically improved over querying the XML data source directly.

Note: I found that if you have more than around 1.6 millions lines in an XML (~50MB), it will hit a memory cap in SSMS (not a limitation of the SQl Server sevice, but of SQL Server Managment Studio). If you're having to work with extremely large files (I had one XML file that was larger tham 2.6GB), trial the query in SSMS with a smaller XML file (or subset of the data), then save the query and run it through sqlcmd in a CMD window. The memory limitation is only imposed through SSMS, and not the command line.

Thanks again!

 - Simon.

Monday, January 16, 2017 - 7:49:08 AM - Greg Robidoux Back To Top (45345)

Hi Harry,

check to see if the SQL Server service account has access to the folder you are trying to use.

You can use xp_cmdshell and run this command.

xp_cmshell 'dir 'C:\Users\Administrator\Desktop\'

If this comes back with permission issues then you know that is the problem.

-Greg

Monday, January 16, 2017 - 4:11:36 AM - Harry Back To Top (45343)

Hello. I have a problem with this query

CREATE TABLE OPEN_XML

(

IDINT IDENTITY,

XML_DATAXML,

LOADED_DATE_TIMEDATETIME

CONSTRAINT PK_OPEN_XMLPRIMARY KEY(ID)

)

 

INSERT INTO OPEN_XML(XML_DATA,LOADED_DATE_TIME)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 

FROM OPENROWSET(BULK 'C:\Users\Administrator\Desktop\1.txt', SINGLE_BLOB) AS x

My error is ( Cannot bulk load because the file "C:\Users\Administrator\Desktop\1.txt" could not be opened. Operating system error code 5(Access is denied.) please help me thanks a lot

Friday, December 2, 2016 - 6:16:58 AM - Lucy Back To Top (44884)

 hello

I developed a procedure that import xml files to sql table with xml columns and read informations this columns to write information at other sql tables but I have a problem.

XML files have different namespaces. I developed different procedures to read different xml namespaces.

Is there a method at tsql to identify the namespace to be able to distinguish which procedure I should execute?

 

thanks

Thursday, November 17, 2016 - 10:23:39 AM - Greg Robidoux Back To Top (43788)

Hi Dan,

this is probably a permissions error.  Check to make sure your SQL Server service account has access to the folder.

-Greg

Thursday, November 17, 2016 - 10:02:53 AM - dan Back To Top (43785)

 Hi Arshad,

Thank you for this articole. I follow it step by step but I got the following error:

 Msg 4861, Level 16, State 1, Line 11
Cannot bulk load because the file "C:\Users\***\Desktop\OPEN_DATA_SPLIT\OPEN_DATA_1.xml" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).
Any suggestion, please. Thanks Dan.

 

Thursday, October 20, 2016 - 9:29:02 AM - Ali Syed Back To Top (43599)

Good day!

nice job Mr.Arshad!

could you guide me in some issue, if i want to transfer my result to another table , how could come it be done by using yours tips.

thanks in advance.

Thursday, September 1, 2016 - 8:26:17 AM - Michael Johns Back To Top (43245)

Now THAT was brilliant!!!!

A+ thank you

MJ 

 

Tuesday, August 2, 2016 - 4:29:47 AM - Mahender Singh Back To Top (43028)

 Hiiii

I have lot of XML file.I want to save all XML file data into SQL server DATABASE.Can possible,If possible please suggests me.

SQL Server is 2008R2 and each XML file size is less than 2MB.

Best Regards

Mahender Singh

Sunday, July 17, 2016 - 10:26:40 PM - eysy Back To Top (41904)

Hello Mr Arshad, I've met a problem when I was trying to import a XML file whose size is larger than 400M. The sql server system reminds me with "System.OutOfMemoryException" when I excute the "SELECT" query. Could you give me any suggestions?

Thanks

Regards

Thursday, July 14, 2016 - 9:51:02 AM - Muqit Zoarder Back To Top (41888)

 

 Hello Mr Arshad, Please guide me how I can update one value inside all XML file stored in one database table in xml type but at once? Like if I have organisation name as a element and i want to change organisation_name=Apple to another name at once , so what is the query?

 

 

Thanks

 

Regards

 

Md Abdul Muqit Zoarder

Sunday, July 10, 2016 - 8:32:06 PM - Srujan Back To Top (41858)

I have zero experience with stored procedures, this article worked like a charm! Well guided with step by step procedure. Thank you.

 

Thursday, December 10, 2015 - 12:13:04 AM - Salman Momin Back To Top (40227)

I am working on a very basic xml file like

<Root><a>

<b>1</b>

</a>

<c><d>

<e>salman</e>

</d></c></Root>

 

where i have more no. of parent nodes.

your technic of '../../' did not help. please help me with something

Monday, December 7, 2015 - 5:24:44 AM - Simon Back To Top (40205)

If you use namespaces you must include them in the

sp_xml_preparedocument as an extra parameter,
and in the OPENXML statement as part of the element path.

http://stackoverflow.com/questions/26672670/openxml-import-xml-file-to-sql-server-table

Thursday, December 3, 2015 - 3:08:34 AM - Abhilash Back To Top (40187)

Currently I am in Grand parent level. I have be in that level and retrive of Child.

 

Here for example:

 

I am in Customers, without navigating into deeper level I want to retrive OrderID. 

 

Please help me out. I have this requirement. Very urgent

Monday, October 19, 2015 - 12:40:56 AM - Varadarajan R Back To Top (38927)

In continuation of my earlier post, please find below the SELECT statement.

 

SELECT NAME,PARENT,ISBILLWISEON, STATENAME, BILLDATE, NAME, BILLCREDITPERIOD, ISADVANCE , OPENINGBALANCE

FROM OPENXML(@hDoc, 'TALLYMESSAGE/LEDGER/BILLALLOCATIONS.LIST')

WITH 

(

Name[varchar] (100)'../@NAME',

Parent[varchar] (100) '../PARENT',

IsBillWiseOn[varchar] (3)'../ISBILLWISEON',

StateName[varchar] (30) '../STATENAME',

BillDatedate 'BILLDATE',

BillNumber[varchar] (30) 'NAME',

BillCreditPeriod [varchar] (30) 'BILLCREDITPERIOD',

IsAdvance  [varchar] (3) 'ISADVANCE',

OpeningBalance DECIMAL(18,2) 'OPENINGBALANCE'

)

 

Sunday, October 18, 2015 - 10:05:16 PM - Varadarajan R Back To Top (38926)

Thanks Arshad for a wonderful article.  It is simplifying my task a lot.  However I have one challenge.  If you can help me overcome this, it would be wonderful.

My XML fragment is as below.

   

     NAME="3m Electo and Communication Pvt Ltd -Wo-P1516-1078-1309-Supply Bills" RESERVEDNAME="">

     Karnataka

     3m Electo and Communication Pvt Ltd-Supply Bills

     30 Days

     Yes

     -49985.00

     

       

        <NAME>3m Electo and Communication Pvt Ltd -Wo-P1516-1078-1309-Supply Bills

       

       1033

     

     

       20140827

       <NAME>AE1415S1200

       26-Sep-2014

       No

       -27984.00

     

     

       20150311

       AE1415S1564

       10-Apr-2015

       No

       -9262.00

     

     

       20150317

       <NAME>AE1415S1570

       16-Apr-2015

       No

       -12739.00

     

     

   

 
Challenge is the xml tag name is same at multiple places. See items marked in Bold.  When I use your suggested method, I get the values for the repeated xml tags as the first xml tag value.  For example, for NAME under BILLALLOCATIONS.LIST I get "3m Electo......" instead of say AE1415S1570.
 
Request your help.
 
Thanks in Advance
 
Vararadarajan R
 

Sunday, September 13, 2015 - 4:18:31 AM - Auguste Back To Top (38664)

Hello Arshad,

 

This information is very helpful.

 

I followed the steps but I got all the rows with only NULL values.  My XML file have 19 columns and over 1000 rows, but I query only 7 columns that I need on my SQL tables.

 

Am I required to query for all the columns?

 

Thanks,

 

Auguste.

Wednesday, August 26, 2015 - 7:51:41 PM - Michael Back To Top (38544)

Thank you so much  Arshad Ali. Almost 2 years on from your last update and it's still proving invaluable. That is without doubt one of the best, most helpful posts I have ever read. Clear, concise... and it works!

cheers

Michael

Monday, August 24, 2015 - 3:41:49 PM - David Back To Top (38527)

Hello,
Your tips very useful and very helpful,
I have a question debriefing xml namespace,
I have the following xml:

<report xmlns="http://SpecXml/" useStyleVersion="10" expressionLocale="en">
  <modelPath>/content/package[@name='Test']/model[@name='model']</modelPath>
  <drillBehavior />
  <queries>
    <query name="Query1">
      <source>
        <model />
      </source>
      <selection>
        <dataItem aggregate="none" rollupAggregate="none" name="Report Name">
          <expression>[Test].[Report Vs Package].[Report Name]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
        <dataItem aggregate="none" rollupAggregate="none" name="Check For Upgrade">
          <expression>[Test].[Report Vs Package].[Check For Upgrade]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
      </selection>
      <detailFilters>
        <detailFilter>
          <filterExpression>[Report Name]='ddd'</filterExpression>
        </detailFilter>
        <detailFilter>
          <filterExpression>[Check For Upgrade]='654312'</filterExpression>
        </detailFilter>
      </detailFilters>
    </query>
    <query name="Query2">
      <source>
        <model />
      </source>
      <selection>
        <dataItem aggregate="none" rollupAggregate="none" name="Package Name">
          <expression>[Test].[Report Vs Package].[Package Name]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
        <dataItem aggregate="none" rollupAggregate="none" name="New Query Item">
          <expression>[Test].[Report Vs Package].[New Query Item]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
      </selection>
      <detailFilters>
        <detailFilter>
          <filterExpression>[Test].[Report Vs Package].[New Query Item]=123</filterExpression>
        </detailFilter>
      </detailFilters>
    </query>
    <query name="Query3">
      <source>
        <model />
      </source>
      <selection>
        <dataItem aggregate="none" rollupAggregate="none" name="Role">
          <expression>[Test].[Cognos Roles].[Role]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
        <dataItem aggregate="none" rollupAggregate="none" name="User Name">
          <expression>[Test].[Cognos Roles].[User Name]</expression>
          <XMLAttributes>
            <XMLAttribute output="no" name="RS_dataType" value="3" />
            <XMLAttribute output="no" name="RS_dataUsage" value="attribute" />
          </XMLAttributes>
        </dataItem>
      </selection>
    </query>
  </queries>
  <reportName>TestFild</reportName>
</report>

I question him in the following manner:


WITH XMLNAMESPACES (DEFAULT 'http://SpecXml/')
SELECT so.value ('@name[1]','varchar(1000)') QueriName
    ,so.value ('(selection/dataItem/@name)[1]','varchar(1000)') FieldName
    ,so.value ('(selection/dataItem/expression)[1]','varchar(1000)') Field
    ,so.value ('detailFilters[1]','varchar(1000)') Filter
     FROM [CS_Information].[dbo].[XmlSpec]
     OUTER APPLY [SpecXml].nodes('//queries/query') da(so) WHERE SpecXml_Id = 1989


The problem is that I get only the first year and not all fields,
If I am investigating this:

WITH XMLNAMESPACES (DEFAULT 'http://SpecXml/')
SELECT so.value ('@name[1]','varchar(1000)') QueriName
    ,so.value ('(selection/dataItem/@name)[1]','varchar(1000)') FieldName
    ,so.value ('(selection)[1]','varchar(1000)') Field
    ,so.value ('(detailFilters)[1]','varchar(1000)') Filter
     FROM [CS_Information].[dbo].[XmlSpec]
     OUTER APPLY [SpecXml].nodes('//queries/query') da(so) WHERE SpecXml_Id = 1989


I get all the fields in a single line when I expect to receive any new message field without multiplying the data,
I would thank you if you have an idea how to help me,
Thank you,
David

Friday, August 7, 2015 - 5:16:29 AM - Tonny Sigmond Back To Top (38402)

After looking everywhere I got into this code, very short and working like a charm, Thanks

Wednesday, August 5, 2015 - 7:42:58 PM - Travis Back To Top (38389)

THANKS!!!!

Thursday, April 30, 2015 - 10:39:22 AM - Tahir Khalid Back To Top (37072)

Masha'Allah Tabark'Allah Ahki this was exactly what I was looking for.

Monday, April 27, 2015 - 3:17:13 PM - Scott Back To Top (37043)

I was in a real bind and this article had exactly what I needed to write a flexible stored procedure that will be able to handle all of the xml files that our project will be receiving this year.

Friday, April 10, 2015 - 4:38:09 AM - Art Back To Top (36888)

Hi Arshad Ali 

Thanks for the super article. 

works perfect. 

 

could I ask you to also put here the use and declaration of XML namespaces 

how to query XML data columns that contain XML namespaces a: or mp:

Thursday, April 9, 2015 - 2:04:53 PM - Sridevi Kolluri Back To Top (36883)

Hi Arshad,

 

Thanks very much for the article! This is exactly what I am looking for. well, mostly.

Now, I want the data to be inserted into different tables - Customer, Order and OrderDetail. Each with a Foreign key to the corresponding row in the parent table. The tables all have Primary key as an auto incremented ID column. In my case, the elements do not have an "ID" element that i can use instead.

Can you (or anyone else who reads this question) please tell me or give me ideas on how I can do this?

Thanks!

Sridevi

 

Friday, February 20, 2015 - 9:57:44 AM - Cecilia W Back To Top (36301)

Hi,

This article was very good, now I understand how I can do. I used the same file data and created the same table as in the example.
But when I run the script which contains from declare ...  to EXEC sp_xml_removedocument @hDoc nothing happens, no output!
I use SQLExpress, is this the reason?

 

Best regards,

 

Tuesday, December 16, 2014 - 11:33:08 AM - TheCurt Back To Top (35618)

khadijah:

INSERT XMLwithOpenXML

      (XMLData,LoadedDateTime)

VALUES((SELECT * FROM OPENROWSET(BULK 'C:\TEMP\xmlFile1.xml',SINGLE_BLOB) AS SRC),GETDATE())

     ,((SELECT * FROM OPENROWSET(BULK 'C:\TEMP\xmlFile2.xml',SINGLE_BLOB) AS SRC),GETDATE())

     ,((SELECT * FROM OPENROWSET(BULK 'C:\TEMP\xmlFile3.xml',SINGLE_BLOB) AS SRC),GETDATE());

 

My routine is *slightly* different from that mentioned in the tip, but after a couple small alterations, the above code should help with loading more than one XML file in the table defined in the tip. Make certain that you select the proper XML record based on the Id column.

Monday, November 24, 2014 - 1:44:09 AM - khadijah Back To Top (35377)

hi,

 

how about if we have multiple xml files need to be loaded into sql in 1 query.

Below query only can load 1 xml file per query executed

 

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;

 

Wednesday, September 3, 2014 - 10:09:23 AM - sakthi Back To Top (34365)

hi sir,

if i want to index an address which is in a xml  file how do i do it...help me kindly.......

Monday, August 25, 2014 - 10:11:43 AM - Thejus T V Back To Top (34248)

Thanks man.

 

It really helped me

Wednesday, August 20, 2014 - 10:59:43 AM - Scott Back To Top (34209)

Try changing "reviewpage nvarchar(1000)" in your original query to " reviewpage nvarchar(1000) 'inlinefiles/inlinefile' ".

Saturday, August 16, 2014 - 4:34:38 AM - Prasanna Back To Top (34164)

Is there any way to fill that query result in to table so that i can query it .

Tuesday, August 12, 2014 - 2:02:57 PM - Scott Back To Top (34100)

I don't know what happened to the line breaks, I'll try again:

with (pageid nvarchar(10),
       name nvarchar(1000),
       newestreviewdate varchar (50),
       oldestreviewdate varchar (50),
       average_rating_decimal float,
       fullreviews int,
       -- Get the value of the reviewpage attribute
       reviewpage smallint 'inlinefiles/inlinefile/@reviewpage',
       -- Get the text value of the inlinefile node
       inlinefile nvarchar(1000) 'inlinefiles/inlinefile'
          );

 

Tuesday, August 12, 2014 - 2:00:50 PM - Scott Back To Top (34099)

Ameya:  Can  you please help me modify my select query

The XPATH query in the OPENXML function is the starting point for all the field queries.  The fields you have listed in the WITH clause don't have any individual XPATH specifications, so they work only for named nodes that are children of the main query.  The last column of your query then is asking for /products/product/reviewpage, which does not exist.  reviewpage is an attribute of the inlinefile node, which is a child of inlinefiles, which is a child of products.  The WITH clause could be modified to this:

with (pageid nvarchar(10), name nvarchar(1000), newestreviewdate varchar (50), oldestreviewdate varchar (50), average_rating_decimal float, fullreviews int, -- Get the value of the reviewpage attribute reviewpage smallint 'inlinefiles/inlinefile/@reviewpage', -- Get the text value of the inlinefile node inlinefile nvarchar(1000) 'inlinefiles/inlinefile' ); 
reviewpage inlinefile 1 pwr/q9c4233d/inline/00/00/123332__1-en_US-1-reviews.html

Monday, July 28, 2014 - 1:23:42 PM - Matt Back To Top (33905)

Thanks for this! Very helpful!

Wednesday, July 9, 2014 - 2:15:47 PM - Ameya Back To Top (32611)

Hello Arshad, 

Thanks for the beautiful article! Appreciate the way you have explained each and every step. I need your help on one of XML import task I am working on. I am importing only selected elements from the XMl into relational SQL table.Referring to your article,  I am quit successfull in importing most of the elements I need , except one.  

Also, I am quiet sure,  I am making some mistakes in writing the  'XPATH' in the Select query. It fails to bring back one element from the XML , which is at the bottom of the doc.

 

From this doc, I managed to import all  the elements in Italics. Hoowever one last element at the very bottom of the doc is not getting imported in SQL table. I have made it bold for easy catch.

Here is XML snapshot  ( Sorry for pasting the long XML doc.)

<?xml version="1.0" encoding="UTF-8"?>

<products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<product xsi:type="ProductWithReviews" locale="en_US">

<pageid>123332-1</pageid>

<name>Sierra striped denim Dress</name>

<smallstarimagelocation>pwr/engine/images/stars_small.gif</smallstarimagelocation>

<largestarimagelocation>pwr/engine/images/stars.gif</largestarimagelocation>

<newestreviewdate>2008-11-01</newestreviewdate>

<oldestreviewdate>2008-03-26</oldestreviewdate>

<averageoverallrating>4</averageoverallrating>

<average_rating_decimal>3.9</average_rating_decimal>

<fullreviews>40</fullreviews>

<confirmstatusgroup>

<confirmstatus>Verified Purchaser</confirmstatus>

<confirmstatus>Verified Reviewer</confirmstatus>

<confirmstatus>Unverified</confirmstatus>

</confirmstatusgroup>

<taggroup key="bestuses" name="Best Uses">

<tag isuseradded="true" count="1">Any time</tag>

<tag isuseradded="false" count="2">Casual wear</tag>

<tag isuseradded="false" count="32">Casual work days</tag>

<tag isuseradded="false" count="17">Comfort-oriented</tag>

<tag isuseradded="false" count="16">Dates / night out</tag>

<tag isuseradded="false" count="8">Leisure wear</tag>

<tag isuseradded="false" count="20">On the weekend</tag>

<tag isuseradded="false" count="3">Special occasions</tag>

<tag isuseradded="false" count="14">To school</tag>

<tag isuseradded="false" count="19">To work</tag>

</taggroup>

<taggroup key="comfortvsstyle" name="Comfort vs Style">

<tag isuseradded="false" count="2">Comfort driven</tag>

<tag isuseradded="false" count="1">Style driven</tag>

</taggroup>

<taggroup key="cons" name="Cons">

<tag isuseradded="true" count="1">A bit large in the bust</tag>

<tag isuseradded="false" count="1">Cheap fabric</tag>

<tag isuseradded="false" count="1">Color not as expected</tag>

<tag isuseradded="true" count="1">Does not look good on</tag>

<tag isuseradded="true" count="1">Fabric too stiif</tag>

<tag isuseradded="true" count="1">Gapes at the chest</tag>

<tag isuseradded="false" count="2">Low quality</tag>

<tag isuseradded="true" count="1">Not flattering</tag>

<tag isuseradded="true" count="1">Not for my body type</tag>

<tag isuseradded="false" count="8">Poor fit</tag>

<tag isuseradded="false" count="2">Rides-up</tag>

<tag isuseradded="true" count="1">Sized one size smaller</tag>

<tag isuseradded="true" count="1">Sleeves are VERY tight</tag>

<tag isuseradded="true" count="2">Sleeves too tight</tag>

<tag isuseradded="true" count="1">Tight/gapes in front.</tag>

<tag isuseradded="true" count="1">Uncomfortable fit</tag>

<tag isuseradded="false" count="4">Wrinkles easily</tag>

</taggroup>

<taggroup key="describeyourself" name="Describe Yourself">

<tag isuseradded="false" count="35">Bargain shopper</tag>

<tag isuseradded="false" count="19">Career</tag>

<tag isuseradded="false" count="30">Casual dresser</tag>

<tag isuseradded="false" count="19">Classic style</tag>

<tag isuseradded="true" count="1">Disabled w/fixed income</tag>

<tag isuseradded="false" count="7">Eclectic</tag>

<tag isuseradded="false" count="11">Sexy</tag>

<tag isuseradded="false" count="5">Trendy style</tag>

</taggroup>

<taggroup key="fit" name="Fit">

<tag isuseradded="false" count="1">Feels too big</tag>

<tag isuseradded="false" count="7">Feels too small</tag>

<tag isuseradded="false" count="29">Feels true to size</tag>

</taggroup>

<taggroup key="length" name="Length">

<tag isuseradded="false" count="1">Feels too long</tag>

<tag isuseradded="false" count="4">Feels too short</tag>

<tag isuseradded="false" count="32">Feels true to length</tag>

</taggroup>

<taggroup key="pros" name="Pros">

<tag isuseradded="false" count="35">Attractive</tag>

<tag isuseradded="false" count="30">Comfortable</tag>

<tag isuseradded="false" count="11">Doesn't wrinkle</tag>

<tag isuseradded="false" count="27">Durable</tag>

<tag isuseradded="false" count="24">Flattering</tag>

<tag isuseradded="true" count="1">Good price / value</tag>

<tag isuseradded="false" count="19">High quality</tag>

<tag isuseradded="false" count="2">Nice fabric</tag>

<tag isuseradded="false" count="17">Versatile</tag>

</taggroup>

<taggroup key="waist" name="Waist">

<tag isuseradded="false" count="1">Feels too big</tag>

<tag isuseradded="false" count="8">Feels too small</tag>

<tag isuseradded="false" count="28">Feels true to size</tag>

</taggroup>

<bottom_line_yes_votes>32</bottom_line_yes_votes>

<bottom_line_no_votes>7</bottom_line_no_votes>

<customerimages>false</customerimages>

<customervideos>false</customervideos>

<inlinefiles>

<inlinefile reviewpage="1">pwr/q9c4233d/inline/00/00/123332__1-en_US-1-reviews.html</inlinefile>

</inlinefiles>

</product>

 
Here is the Select query, I am using :
 
Declare @x xml
 
select @x=t 
from openrowset (bulk 'D:\XML\review_data_summary_20140709_083004.xml',single_blob) as testXML(t)
 
 
declare @hdoc int
 
Exec sp_xml_preparedocument @hdoc Output , @x
 
 
 
Select * into test_XMLimport_PR_7_9_14
from openxml ( @hdoc,'/products/product',2) 
with (pageid nvarchar(10),
       name nvarchar(1000),
       newestreviewdate varchar (50),
       oldestreviewdate varchar (50),
       average_rating_decimal float,
       fullreviews int,
       reviewpage nvarchar(1000)
          )
 
exec sp_xml_removedocument @hdoc
 
 Can  you please help me modify my select query with the correct Xpath so that It will return the element ( marked in bold ) from the XML doc ?? 
I tried adding the node of that element in the xpath , but it is returing null values.
 
Thanks for your help!!
 
Ameya Thakur
 

Friday, June 6, 2014 - 9:17:38 AM - Mahalakshmi Back To Top (32118)

I'm a fresher. I read some basics in XML and XSD. I was given a task of Validating XSD against XML document using stored procedure. They have given both XML and XSD file. I need to validate and generate error message using stored procedure. I don't have any idea about that. Please help me. 

Many Thanks in advance.

Tuesday, June 3, 2014 - 9:31:48 AM - Avinash Gunjuluri Back To Top (32057)

Thanks for the article, how do we approach it if my xml has name spaces in it?any idea?

Friday, April 4, 2014 - 2:03:47 PM - Oscar Back To Top (29976)

Hi Arshad,

 

Thanks a lot for this great article.
I just have one question since I'm new on this.

Why you use "@" in the Customer attributes?
You remarked this in your post, but since I'm new on this, I don't understand how it changes the result.

 

Thanks and regards,

Oscar 

Friday, March 21, 2014 - 1:00:25 PM - Meenakshi Back To Top (29831)

Thanks Arshad, Very nice article. 

 

I am using the same concept for loading my XML to Table. Everything is fine except that when my node doesnt have full heirarchy underneath that, records are not getting loaded to the table. e.g I have below heirarchy.

so in this example highlighted bold values are going in the table but the last line that does'nt have grade2 and grade3 information is not going in the table. Can you please suggest what is wrong? I am using exact code you have explained above. Thanks for your help.

Regards Meenakshi 

<grade1 name="abc" subname="xyz">

     <grade2 name="123" subname="456">

              <grade3 name="dfg" subname="897">

               </grade3>

      </grade2>

</grade1>

<grade1 name="abc1" subname="xyz1"/>

 

Friday, March 7, 2014 - 7:28:10 AM - raj Back To Top (29675)

Hi, Nice post but i have one doubt in above example if my 'ROOT' contains namespace how to include that in stored procedure. while am using above code with namespace jus a name of table oly showing but the values not inserting. looking for some suggestions

 

Thanks in advance

 

 

Tuesday, February 18, 2014 - 4:41:29 AM - Pratik Prakash Back To Top (29486)

Excellent description Mr. Arshad, codes are working superbly.

We have recently decided to import data in the form of XML, and your reference is perfectly matching our requirement.

Friday, January 24, 2014 - 2:59:46 AM - Anupam Santra Back To Top (28211)

 

I'm unbale to featch the data from xml file into relational table , kindly help me.Given below my XMl File and query.

My Table Desc.

AppId MobileNo PickupId CirSpoke DistId InsDt InsBy DocType
1 9051966812 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19
2 9051965635 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19
3 9051965245 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19

 

My XML File.

<Appi>

  <Appi>

    <AppId>1</AppId>

    <MobileNo>9051966812</MobileNo>

    <PickupId>123</PickupId>

    <CirSpoke>KOLDST</CirSpoke>

    <DistId>2</DistId>

    <InsDt>2012-02-01T19:33:12.980</InsDt>

    <InsBy>VFKOL\animesh_kundu</InsBy>

    <DocType>19</DocType>

  </Appi>

  <Appi>

    <AppId>2</AppId>

    <MobileNo>9051965635</MobileNo>

    <PickupId>123</PickupId>

    <CirSpoke>KOLDST</CirSpoke>

    <DistId>2</DistId>

    <InsDt>2012-02-01T19:34:37.257</InsDt>

    <InsBy>VFKOL\animesh_kundu</InsBy>

    <DocType>19</DocType>

  </Appi>

  <Appi>

 

My Result is coming blnk with given query.

Declare @xml as XML,@adhoc as int,@sql NVARCHAR(Max)

 

select @xml=xmlfld from XMLtab 

EXEC sp_xml_preparedocument @adhoc output,@xml

 

select appid,MobileNo from openxml(@adhoc,'APPI/Appid')

WITH

(appid int '@appid',

mobileno varchar(10) '@mobileno')

 

EXEC sp_xml_removedocument @adhoc

GO

 

 

Tuesday, January 21, 2014 - 11:23:56 PM - audadi Back To Top (28166)

Nice!!

Simple&clear..Thanks arshad

Thursday, December 26, 2013 - 11:14:54 PM - NJ Back To Top (27889)

Thank you Arshad, I don't usually comment on these things, but your explanation was awesome!  Your examples were easy to comprehend and adapt to my needs.  Fabulous job.

Thursday, December 19, 2013 - 5:18:55 AM - Bothwell Back To Top (27844)

Arshad,

Thanks for the well explained article

Wednesday, October 23, 2013 - 7:29:14 PM - Arshad Back To Top (27249)

Hi Kevin,

Please refer this tip, it might be helpful for you:

http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

 

Tuesday, October 22, 2013 - 5:46:32 AM - kevin Back To Top (27216)

good article.

if i need modify the data in the xml column,can you give some example,because i plan to develop a sql lib to other user which will allow them to retrieve the xml node and attribute data and modify it earsily.

i had google a lot and found their almost no example as your spread helpful.

thanks

Thursday, October 10, 2013 - 7:22:42 AM - Bashir I. Mukaddam Back To Top (27099)

Dear Arshad,

I am looking for VBScript code to open an XML file and use SQL statements like SELECT to retrieve data from XML file.

Any suggestions?

Regards,

Bashir I. Mukaddam, Dhahran, Saudi Arabia

[email protected]

Monday, September 30, 2013 - 1:06:49 PM - bub Back To Top (26993)

Very nice and simple indeed!

I don't manage to process XML's with tags like

<cbc:IdentificationCode>BE</cbc:IdentificationCode>

If I would use

WITH IdentificationCode [varchar] (100) '@cbc:IdentificationCode'

I get "XML Parsing error : Reference to undeclared namespace prefix: 'cbc'

Any hint/tip would be greatly appreciated!

Thanks for the code!

bub

 

 

Thursday, August 1, 2013 - 6:39:46 AM - Murlidhar Back To Top (26084)

I am using this SQL Query to import XML data to SQL

INSERT INTO tbl_XMLFile(Business,XMLFileName,XMLData, LoadedDateTime)
SELECT 'IB', 'IB_WebAppAllSitesAndUsers.xml',CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\SharePoint_Reporting\SharePoint_XML\FromLicia\AllSitesAndUsers_30July2013\IB_WebAppAllSitesAndUsers.xml', SINGLE_BLOB) AS x;

but my XML include some special character like

&#xD;&#xA;

because of this i am getting this below mentioned error

XML parsing: line 164852, character 213, illegal xml character

please guide me what to do.

 

 

Friday, July 12, 2013 - 11:44:49 AM - Neil Back To Top (25817)

Wow, this was a lot better explanation than Microsoft's page!

Thanks a bunch, now eveyone at work loves me!

Wednesday, April 24, 2013 - 2:48:20 AM - Reza Hossain Back To Top (23534)

Very nice and simple

Monday, April 22, 2013 - 2:17:50 AM - muruga Back To Top (23462)


<ROOT>
 <Customers>
  <Customer CustomerName="Arshad Ali" CustomerID="C001">
   <Orders>
    <Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
     <OrderDetail Quantity="5" ProductID="11"/>
     <OrderDetail Quantity="12" ProductID="11"/>
     <OrderDetail Quantity="10" ProductID="42"/>
    </Order>
   </Orders>
   <Address>Address line 1,2,3</Address>
  </Customer>

  <Customer CustomerName="Paul Henriot" CustomerID="C002">
   <Orders>
    <Order OrderDate="2011-07-04T00:00:00" OrderID="10245">
     <OrderDetail Quantity="12" ProductID="11"/>
     <OrderDetail Quantity="10" ProductID="42"/>
    </Order>
   </Orders>
   <Address>Address line 5,6,7</Address>
  </Customer>
 </Customers>
</ROOT>

Wednesday, March 20, 2013 - 6:52:24 AM - manoj Back To Top (22895)

nice article arshad...very helpful

 

Tuesday, March 19, 2013 - 5:35:52 PM - Rajesh Back To Top (22880)

Very good article Arshad. I played little bit - really helpful for my upcoming project. Thanks -- Rajesh

Tuesday, March 19, 2013 - 10:34:54 AM - Cary Davis Back To Top (22871)

Nice article Arshad.  














get free sql tips
agree to terms