Simple way to read XML files in SQL Server

SQL Server 2000 makes it easier to export data in XML, but it can be cumbersome to import and process XML data in SQL Server 2000.

If you refer to the books Online (BOL), you will find relevant entries, including OPENXML and OPENROWSET. All of these examples support the use of XML literals as declared variables, which are convenient for users who often work with text, but not for developers who want to read XML files in development and handle them accordingly. To deal with such a problem, it may be best to analyze it from the inside to the outside.

OPENXML is a rowset function (that is, returning a rowset) that works like the rowset function OPENQUERY and OPENROWSET. Using OPENXML, you can perform joins operations on XML data without first importing data. You can also use it in conjunction with inserts, SELECT, update, and delete operations. However, to use OPENXML, you must perform two tasks that OPENQUERY and OpenRowset do not need. Both of these tasks require two system storage processes. The first is sp_xml_preparedocument, which reads the specific XML text and extracts its contents into memory. The syntax is as follows:

sp_xml_preparedocument @hdoc = OUTPUT,
[, @xmltext = ]
[, @xpath_namespaces =

The specific parameters are as follows: @hdoc: A handle to an area of memory (which is equivalent to a pointer from a function), where the relevant data is stored. Note that this is an output variable that will contain a handle to the contents of the XML file at the memory address when the process is run. Because you need to use this result later, be sure to save it; @xmltext: The XML text you want to process; @xml_namespaces: any namespace index required for normal operation of your XML data (namespace References). Note that any URLs that appear here need to be enclosed in angle brackets (<>), assuming that the parameters passed are valid, and that the XML document exists, your XML data is stored in memory. Now you can call sp_xml_preparedocument, pass the variable that holds the XML file, and then execute the OPENXML. The syntax is as follows:

OPENXML(idocint [in],rowpatternnvarchar[in],[flagsbyte[in]])
[WITH (SchemaDeclaration | TableName)]

Note: There is not enough text in this article to describe the parameters that OpenXML received. See Bol for more information. Find OPENXML in Transact-SQL Reference.

Now we have reached the final step. All that remains is to import an actual XML file into the SQL and process it (very fast why none of the BOL examples involve this critical part). (I must thank my colleague Billy Pang for the help he has given me.) He helped me solve the problem and gave the code--although I needed to cut the code for this article. Thanks, billy!. The basic trick is to read the file line by text. Then connect all the read rows to a large varchar variable. Finally, pass the variable to the code described earlier.

Here's the code to read the file and store its contents in 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

Now you've got all the contents of the file in the variable @filecontents variable. All you need to do is pass the variable through the @xmltext parameter to sp_xml_preparedocument, and then call OPENXML.

With this workaround, a variety of processing of XML documents is possible. You can connect an XML document to a SQL table without importing the data, and then insert, pdate, and delete the data.