XML-SQL Server 2000 makes it easier to export data, but in SQL Server 2000 XML data import and process is somewhat cumbersome.
If you refer to Books Online (BOL), you will find the relevant entries, including the OPENXML and OPENROWSET.All of these examples support the XML text has been declared as a variable, which for users often deal with the text is very convenient, but who want to read the XML file in the development and the corresponding treatment of developers not true.Dealing with this problem, perhaps the best from the inside to the outside of their analysis.
OPENXML is a rowset function (ie return a rowset), it works similar to the rowset function OPENQUERY and OPENROWSET.XML data using OPENXML can perform JOINs operations without having to first import the data.You can also use it with the INSERT, SELECT, UPDATE, and DELETE operations such as joint use.However, to use OPENXML, you must perform two OPENQUERY and OPENROWSET does not need the task.These two tasks require two system memory processes.The first is sp_xml_preparedocument, it will read specific text and extract the contents of XML into memory.The syntax is as follows:
sp_xml_preparedocument @ hdoc = OUTPUT,
[, @ Xmltext =]
[, @ Xpath_namespaces =
Specific parameters are as follows: @ hdoc: a handle pointing to a memory region (from the role of point of view equivalent to a pointer), the relevant data is stored here.Note that this is an output variable, when the process runs, the variable will contain the XML files point to handle the contents of the address in memory.Because you need to use the results in the next, so make sure to save them; @ xmltext: In fact you want to handle the XML text; @ xml_namespaces: For normal operation of your data in any required XML namespace index (namespace references.)Note that any URL here need to use sharp brackets (<">) enclosed; assume that these parameters passed are valid, and the XML document exists, then your XML data will be stored into memory.Now you can call sp_xml_preparedocument, XML documents have to pass a variable store, and then perform OPENXML.Syntax is as follows:
OPENXML (idocint [in], rowpatternnvarchar [in], [flagsbyte [in]])
[WITH (SchemaDeclaration | TableName)]
Note: In this article do not have enough words to describe the parameters received by OPENXML.See BOL for more information.In the Transact-SQL Reference in the search OPENXML.
We have now reached the final step.All that remains is an actual XML file into SQL and addressed to the (very swift pace of the BOL example of why all are not involved in this critical part).(I must thank my colleagues for their help Billy Pang. He helped me solve this problem, and gives the code - though I need the code for this article was cut. Thank you, Billy!) Basic skills that willread by a text file line by line.Then connect all the lines read as a large VARCHAR variables.Finally, the variable passed to the previously mentioned code.
The following is read the file and store the contents of the code to a variable:
DECLARE @ FileName varchar (255)
DECLARE @ ExecCmd VARCHAR (255)
DECLARE @ y INT
DECLARE @ x INT
DECLARE @ FileContents VARCHAR (8000)
CREATE TABLE # tempXML (PK INT NOT NULL IDENTITY (1,1), ThisLine VARCHAR (255))
SET @ FileName = 'C: TempCurrentSettings.xml'
SET @ ExecCmd = 'type' + @ FileName
SET @ FileContents =''
INSERT INTO # tempXML EXEC master.dbo.xp_cmdshell @ 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
Variable in the variable @ FileContents now you have received the document in its entirety.Variables have to do is to pass through the @ xmltext parameter sp_xml_preparedocument, then call OPENXML.
With this solution, a variety of XML document processing becomes possible.XML document you can together with the SQL tables without importing the data, then these data INSERT, PDATE, and DELETE and any other operation.