Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Importing and Processing data from XML files into SQL Server tables


By:   |   Read Comments (46)   |   Related Tips: 1 | 2 | 3 | 4 | More > XML

Problem

In my last article, I talked about how you can use an  FTP task in SSIS to download files from an FTP server. But what if the file you have downloaded is an XML file and you need to import this data from the XML file into a SQL Server table? How do you process/parse XML data into SQL Server tables?

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 this:

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.

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

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 and hence 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


Last Update:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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 07, 2015 - 5:24:44 AM - Simon Back To Top

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 03, 2015 - 3:08:34 AM - Abhilash Back To Top

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

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

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

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

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

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 07, 2015 - 5:16:29 AM - Tonny Sigmond Back To Top

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


Wednesday, August 05, 2015 - 7:42:58 PM - Travis Back To Top

THANKS!!!!


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

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

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

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 09, 2015 - 2:04:53 PM - Sridevi Kolluri Back To Top

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

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

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

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 03, 2014 - 10:09:23 AM - sakthi Back To Top

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

Thanks man.

 

It really helped me


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

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

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

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

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

Thanks for this! Very helpful!


Wednesday, July 09, 2014 - 2:15:47 PM - Ameya Back To Top

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 06, 2014 - 9:17:38 AM - Mahalakshmi Back To Top

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 03, 2014 - 9:31:48 AM - Avinash Gunjuluri Back To Top

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


Friday, April 04, 2014 - 2:03:47 PM - Oscar Back To Top

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

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 07, 2014 - 7:28:10 AM - raj Back To Top

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

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

 

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

Nice!!

Simple&clear..Thanks arshad


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

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

Arshad,

Thanks for the well explained article


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

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

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

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

mukaddbi@gmail.com


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

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 01, 2013 - 6:39:46 AM - Murlidhar Back To Top

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

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

Very nice and simple


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


<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

nice article arshad...very helpful

 


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

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

Nice article Arshad.  


Learn more about SQL Server tools