XML and SQL Server

from the Artful SQL Server & Access Tips List

SQL Server 2000 makes it simple to export your data as XML; but importing XML and working with XML files are a little trickier.

If you consult Books Online (BOL), you'll find several relevant entries, including OPENXML and OPENROWSET. All the examples supply the XML text as a declared variable, which is convenient for documentation staff but not for the developer who wants to read an XML file and treat it accordingly. To make sense of this onion, perhaps it's best to approach it from the inside out.

OPENXML is a rowset function (i.e., one that returns a rowset), and it works similarly to the rowset functions OPENQUERY and OPENROWSET. Using OPENXML, you can perform JOINs to XML data without actually importing the data first. You could also use it in conjunction with INSERT. . . SELECT, UPDATE, and DELETE.

However, to use OPENXML, you must perform two tasks that aren't required with OPENQUERY and OPENROWSET. These tasks require two system stored procedures.

The first, sp_xml_preparedocument, reads the specified XML text and moves its contents into memory. Here's the syntax:

sp_xml_preparedocument @hdoc = <integer variable> OUTPUT,
[, @xmltext = <character data>]
[, @xpath_namespaces = <url to a namespace>

The parameters are:
  • @hdoc: a handle (effectively a pointer) to a region of memory where some data of interest resides. Note that it is an output variable—after the procedure runs, the variable will contain the handle to the XML file's contents in memory. Be sure to save the result since you'll need it later.
  • @xmltext: the actual XML text you want to process.
  • @xml_namespaces: any namespace references that your XML requires in order to operate correctly. Note that any URLs here need to be delimited with angle brackets (<>).
If the parameters are valid and the XML text is sound, your XML data will reside in memory. Now call sp_xml_preparedocument, passing the variable containing your XML file, and then OPENXML, whose syntax is a little more involved:

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

Note: There isn't enough space in this tip to describe the parameters that OPENXML accepts. See BOL for more information; search for OPENXML in Transact-SQL Reference.

Now we have the back end in place. All that remains is importing an actual XML file into SQL for processing. (It's curious how all the BOL examples leave this crucial part unattended.)

(I have to acknowledge the invaluable assistance of my colleague Billy Pang, who helped me work through this problem. He inspired the code that follows, though I tailored it for the specific requirements. Thanks, Billy!)

The basic trick is, read the file as text, line by line. Then, concatenate all the resulting rows into a large VARCHAR variable. Finally, pass this variable into the foregoing code.

Here is the code to read the file and store its contents into 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:\Temp\CurrentSettings.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 have the entire contents of the file in the variable @FileContents. All you have to do is plug said variable into the @xmltext parameter to sp_xml_preparedocument, and then call OPENXML.

With this solution in hand, it's possible to perform all the magic that you might want. You can join XML documents to SQL tables without importing the data, and then use the result to INSERT, UPDATE, DELETE for whatever you please.
Return to the Artful SQL Server & Access tips page