🚀 go-pugleaf

http://www.sqlservercentral.com/articles/XML/63687/

Overview
Over the years, XML has grown widely in popularity and has become a popular 
format for exchanging data. Regardless of our preferences, most of us have 
been in situations where we had to import data from XML files. SQL Server 
provides options like OpenXML to do this task. 

I work in both the Microsoft and the Unix worlds and once came across a 
powerful open source XML manipulation utility called XMLStarlet. In this 
article let us explore how we can use XMLStarlet to import XML data from 
files into SQL Server tables. 

XMLStarlet is an open source command line utility available on Sourceforge 
at http://xmlstar.sourceforge.net/. It can be used to perform a variety of 
operations on XML data, all from the command line. One of them is selecting 
data. This feature has been utilized here to import data from XML files 
into a table of our choice by building a wrapper T-SQL stored procedure.

Getting XMLStarlet
The XMLStarlet toolkit is an open source project hosted on Sourceforge.net. 
The download page shows versions for multiple operating systems. We need to 
download the file xmlstarlet-1.0.1-win32.zip from the download page on the 
site http://xmlstar.sourceforge.net/. Extract the Zip file to a folder, say 
C:\XML\xmlstarlet-1.0.1, and we are ready to start. No additional steps are 
required. The executable of our interest is the xml.exe. The zip package 
includes a detailed PDF help file which shows how to perform various 
operations using the toolkit. Of the various functions it is capable of, we 
will only use the Select functionality.

The sample input files and the code for the stored procedures is attached 
in the Resource Files section of this article.

XML File:
<WildLife>
<Creature type="Mammal">
<Name>Cheetah</Name>
<Name>Big Fast Cat</Name>
<Color>Yellow with black spots</Color>
</Creature>
<Creature type="Mammal">
<Name>Brown Bear</Name> 
<Color>Brown</Color>
</Creature>
<Creature type="Reptile">
<Name>Lizard</Name>
<Name>Gecko</Name>
<Color>Green</Color>
</Creature>
<Creature type="Insect">
<Name>Spider</Name>
<Name>Arachnid</Name>
<Color>Black</Color>
</Creature>
<Creature type="Amphibian">
<Name>Turtle</Name>
<Name>Terrapin</Name>
<Name>Turtle</Name>
<Color>Grey</Color>
</Creature>
</WildLife>

Command Syntax:
xml sel -T -t -m "/WildLife/Creature" -v "position()" -o "," -v 
"Name[position()=1]" -o "," -v "@type" -o "," -c "Color" -n WildLife.xml

When we run the above command line on the WIndows prompt we get the 
following output:

Output:
1,Cheetah,Mammal,Yellow with black spots
2,Brown Bear,Mammal,Brown
3,Lizard,Reptile,Green
4,Spider,Insect,Black
5,Turtle,Amphibian,Grey


The above CSV output can now be imported into a table through standard SQL 
Server utilities like BCP or BULK INSERT statement.

The trick is to flatten the XML file to a delimited file and then bulk 
insert the required table with standard utilities. For this purpose we use 
the two stored procedures attached in the Resources section of this 
article. In order to import an XML file into a table we can use the stored 
procedure usp_ImportXML.

Solution
Let us say we need to upload the WildLife.xml file into a table called 
WildLife. The structure of the WildLife table is:

CREATE TABLE WildLife ( Serial_No INT,  Creature_Name VARCHAR(100),  
Creature_Type VARCHAR(100), Creature_Color VARCHAR(100))
The steps below describe how we can import the XML file into this table 
using the usp_ImportXML stored procedure.

Step 1: Prepare a parameter table
The usp_ImportXML stored procedure looks for a temporary table called 
#tblXMLParams. This temp table specifies the record-defining node, the 
delimiter to be used to prepare the intermediate delimited file, the nodes, 
attributes or XPath expressions to be selected from the XML file and their 
order. In the above example, to get the CSV output as shown we create the 
temp table as follows:

IF NOT object_id('tempdb..#tblXMLParams') IS NULLBEGIN DROP Table 
#tblXMLParamsEND
CREATE TABLE #tblXMLParams (XPathOrder int, NodeType VARCHAR(20), 
XPathExpression VARCHAR(500))INSERT INTO #tblXMLParams VALUES 
(0,'Record_Node','/WildLife/Creature')INSERT INTO #tblXMLParams VALUES 
(0,'Delimiter','|')INSERT INTO #tblXMLParams VALUES 
(1,'XPATH','position()')INSERT INTO #tblXMLParams VALUES 
(2,'node','Name[position()=1]')INSERT INTO #tblXMLParams VALUES 
(3,'Attribute','@type')
INSERT INTO #tblXMLParams VALUES (4,'node','Color')
The following values for NodeType are supported:

1. Record_Node - This is the tag that demarcates the start of a record and 
would repeat throughout the XML file. It may contain any number of child 
tags, attributes and nested child tags.

2. Delimiter - This is the delimiter to use instead of the default 
delimiter - comma. Specifying a delimiter other than comma is useful in 
situations where we know that the text we are importing itself contains 
commas. A good delimiter is the pipe - '|'.

3. XPATH - This could be any XPath expression. Here you can use the full 
power of XPATH

4. Node - This type specifies the name of the node from which the text is 
to be selected. XPath can be combined here. For example let us say our XML 
file had Creature tags as shown below:

<Creature type="Mammal">
<Name>Cheetah</Name>
<Name>Big Cheetah</Name>
<Color>Yellow with black spots</Color>
</Creature>

If we want to select only the last Name tag from each Creature, we can use:

INSERT INTO #tblXMLParams VALUES (2,'node','Name[position()=last()]')
Hence we can use the full power of XPATH to select the exact data needed 
from the file.

5. Attribute - This type specifies the attribute whose value is to be 
selected

The temp table #tblXMLParams is used by the stored procedure 
usp_ReadXMLParams to construct the command line parameters to be supplied 
to the XMLStarlet utility. The procedure usp_ReadXMLParams is internally 
used by the usp_ImportXML procedure and we don't need to use it directly 
for the task of importing XML data into tables. Still, if you would like to 
the output of usp_ReadXMLParams, use the code snippet below:

Declare @strXMLStarletCmd VARCHAR(2000)
exec usp_ReadXMLParams @strXMLStarletCmd
Print @strXMLStarletCmd
Why do we use this temp table? Why not a string supplied as an input 
parameter? Because being a generic solution, we do not know beforehand the 
structure of the file we need to import. If strings are used, we may run 
into a situation where the XPATH expressions are too long. Some people may 
prefer to keep the data selection parameters in INI files or in permanent 
tables. They would have to write T-SQL code to create the input string in 
order to use the usp_ImportXML SP. The temp table seems a good solution for 
all the above situations. We can populate the temp table with a minimal 
amount of code. That's a short note about the rationale behind using this 
temp table. Lets go ahead now.....

Step 2: Execute the stored procedure
Once the temp table is created we are ready to run our stored procedure. 
The usp_ImportXML procedure takes the following parameters:

1. @strXMLFileName This is the path of the XML file we want to import

2. @strTableName This is the table to which we want to import the XML data 
into

3. @blnReplace This is an optional Bit variable used to control whether we 
want to append data or delete existing data and import the new data into 
the table. Default behavior is to append the data to the existing table.

4. @strXMLStarletPath This optional variable contains the path to the 
XMLStarlet executable. If it is not supplied, the procedure uses the path 
which is coded in the usp_ImportXML stored procedure. I have coded the path 
as 'C:\XML\xmlstarlet-1.0.1\' as that's the place I unzipped the XMLStarlet 
package.

5. @strDelimiter We can use this optional variable to specify a different 
delimiter than a comma. If it is not supplied, the delimiter is assumed to 
be a comma (,).

To import our WildLife.xml XML file into the WildLife table, we use the 
stored procedure in one of the required formats as shown below:

Case I

Uses default text delimiter (comma)

Data to be appended to the existing table

Declare @strXMLFile Varchar(50),
        @strTable Varchar(50)

Select @strXMLFile = 'C:\XML\WildLife.xml', @strTable = 'WildLife'

exec usp_ImportXML @strXMLFileName=@strXMLFile, @strTableName=@strTable
Case II

Uses default text delimiter (comma)

Existing data to be deleted and new data added to the table

Declare @strXMLFile Varchar(50),
        @strTable Varchar(50),
        @blnOverwrite Bit

Select @strXMLFile = 'C:\XML\WildLife.xml', @strTable = 'WildLife', 
@blnOverwrite = 1

exec usp_ImportXML @strXMLFileName=@strXMLFile, @strTableName=@strTable, 
@blnReplace=@blnOverwrite
Case III

Uses pipe delimiter to internally split the text. This feature is useful 
when the data itself contains commas as part of the text.

Existing data to be deleted and new data added to the table

Declare @strXMLFile Varchar(50),
        @strTable Varchar(50),
        @blnOverwrite Bit,
        @strTextDelimiter Varchar(1)

Select @strXMLFile = 'C:\XML\WildLife.xml', @strTable = 'WildLife', 
       @blnOverwrite = 1, @strTextDelimiter = '|'

exec usp_ImportXML @strXMLFileName=@strXMLFile, @strTableName=@strTable,
                   @blnReplace=@blnOverwrite, 
@strDelimiter=@strTextDelimiter
One thing to note here is that we need to use the same delimiter as was 
used while creating the #tblXMLParams temp table in Step 1. So if we plan 
to use a text delimiter other than the comma, we need to specify it in both 
places:

1. We should add the below statement while creating the parameter temp table

INSERT INTO #tblXMLParams VALUES (0,'Delimiter','|')
2. Pass the same delimiter as the @strDelimiter parameter to the 
usp_ImportXML stored procedure.

Case IV

Uses pipe delimiter to internally split the text. This feature is useful 
when the data itself contains commas as part of the text.

Path to the XMLStarlet exe specified as an input parameter

Existing data to be deleted and new data added to the table

Declare@strXMLFile Varchar(50),@strTable Varchar(50),@blnOverwrite Bit, 
@strTextDelimiter Varchar(1),
@strXMLExePath  Varchar(100)
Select @strXMLFile = 'C:\XML\WildLife.xml', @strTable = 'WildLife', 
@blnOverwrite = 1, @strTextDelimiter = '|', 
@strXMLExePath='C:\XML\xmlstarlet-1.0.1'
exec usp_ImportXML @strXMLFileName=@strXMLFile, @strTableName=@strTable, 
@blnReplace=@blnOverwrite, @strDelimiter=@strTextDelimiter, 
@strXMLStarletPath=@strXMLExePath
The file XML_Import_Usage.txt attached in the Resource Files section shows 
the complete usage code for Case IV.

Conclusion
The XML Bulk Import solution presented in this article can be used to 
easily import XML data from files into database tables. The tables could be 
real tables or temporary tables. In addition to making the import process 
simple, this solution also allows us to use XPath expressions to import 
just the exact data required. So add this to your tool belt and make the 
task of importing XML files easier.
 
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes
Or should I?

You know what I��d like to be? I mean if I had my goddam choice, I��d just be
the catcher in the rye and all.

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.31]