Import XML file into SQL Server

1. Learn the content and basic grammar ofXML file

Reference:

http://blog.csdn.net/chaiyu2002/article/details/5967186

http://blog.csdn.net/jfkidear/article/details/6820642

In a XML file:

` Must have a declaration.

` There is only one root node.

` Other nodes can be multiple.

2. Choose the right way to import

Here I choose OPENXML to automatically readfrom XML file and import into tables.

Reference:

http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

http://www.cnblogs.com/wuming/archive/2010/01/18/1650755.

 

3. Basic grammar of XML

Here I am going to use an example to illustrate theusage of OPENXML

 

–-define ahandle to handle original xml file

declare@xmlText xml

–-define a handle to handle created file

DECLARE @idoc int

--define file path

set@xmlText =(select doc from openrowset(bulk 'D:\related docs\_WAUZZZF88JN001042.xml',single_blob) as t(doc)) 

–-nothing needs to change

EXEC sp_xml_preparedocument@idocOUTPUT,@xmlText

-details as follows

select [VIN] ,[Report_date],[Report_time],[iDEX_User_name],[Report_version]

            fromopenxml(@idoc,'Protokoll',0)

            With(

                [VIN] varchar(50) '//Fahrgestellnummer',

                [Report_date] varchar(50) '//Datum',

                [Report_time] varchar(50) '//Zeit',

                [iDEX_User_name] varchar(50) '//User',

                [Report_version] varchar(50) '//Report-Version'

                )

For the last part of the code, there are 2 things needs to consider:

i)’Protokoll’

This is XPATH and will be introduced in the following section.

ii)0

This is flag value. And there are 4 types of input:

0-Attribute-centered projection

1-Attribute-centered projection. Can use it with XML_ELEMENT.

2-Element-centered projection

8-Currently not used

4. XPATH

This is used to choose the nodes in XML files.

Reference:

http://blog.chinaunix.net/uid-7667983-id-2046478.html

http://www.ruanyifeng.com/blog/2009/07/xpath_path_expressions.html

 

Problems I have encountered:

->Difference:

Node(??): tags

Attribute: data in tags

->Difference between HTML&XML

HTML: for display

XML:for data processing

HTML cannot be utilized to import intodatabase.

 

Whymultiple lines instead of 1 line?

XPATHmisunderstanding!

RowPattern:DO NOT USE /*

ColPattern://NodeName instead of ../NodeName

//is different from/

/:root node

//:normal node

 

 

Whyrepeated loop for 29 times(same content):

Delete //:[Unit_ID] varchar(50) 'Adresse' instead of ‘//Adresse’

//book ：选择所有 book 子元素，而不管它们在文档中的位置。

bookstore ：选取 bookstore 元素的所有子节点。

 

“INSERT statement conflicted with theFOREIGN KEY constraint”

Reference: https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint 

That’s because Iused a foreign key and input data that was not included in that primary keycolumn.