Insert XML document into SQL Server database (2)

Insert SQL Server2000 Database in XML document (1) (http://blog.csdn.net/LoveLion/archive/2006/09/18/1237263.aspx
, Read it as an external file and then process it. By referring to relevant materials, you can use the following method to perform the operation: (assume that there is a product under the C drive of the computer. XML file, whose root node/sub-node is '/productdata/products'). The code for this file is as follows:

<? XML version = "1.0" encoding = "gb2312"?>
<Productdata>
<Products productid = "p030" productname = "Barbie doll" Description = "this is a toy for girls" Category = "Toy" Price = "120" qoh = "10"/>
<Products productid = "p031" productname = "Mini race car" Description = "this is a toy for Boys" Category = "Toy" Price = "130" qoh = "12 "/>
<Products productid = "p032" productname = "babys rattle" Description = "this is a toy for babies" Category = "Toy" Price = "80" qoh = "24"/>
</Productdata>

Run the following program in the query analyzer to insert the above data into the table products:

Declare @ filename varchar (255)
Declare @ execcmd varchar (255)
Declare @ filecontents varchar (8000)
Declare @ IDOC int
Declare @ Y int
Declare @ x int
Create Table # tempxml (Pk int not null identity (1, 1), thisline varchar (255 ))
Set @ filename = 'C:/product. xml'
Set @ execcmd = 'type' + @ filename
Set @ filecontents =''
Insert into # tempxml exec master. DBO. xp_mongoshell @ execcmd
Select @ Y = count (*) from # tempxml
Set @ x = 0
While @ x <> @ Y
Begin
Set @ x = @ x + 1
Select @ filecontents = @ filecontents + thisline from # tempxml where PK = @ x
End
Select @ filecontents as filecontents
Drop table # tempxml

Exec sp_xml_preparedocument @ IDOC output, @ filecontents
Select *
From openxml (@ IDOC, '/productdata/products', 1)
With (productid char (4 ),
Productname varchar (50 ),
Description varchar (100 ),
CATEGORY varchar (10)
)
Insert Products
Select *
From openxml (@ IDOC, '/productdata/products ')
With Products
Exec sp_xml_removedocument @ IDOC

Select * from products

This program first reads the XML document into a string variable @ filecontents cyclically in the unit of action, and creates a temporary table # tempxml to store the string generated by the XML document, after the file is processed, delete the temporary table and add @ filecontents to the method described in the http://blog.csdn.net/LoveLion/archive/2006/09/18/1237263.aspx for processing. to insert external XML documents.