|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:
The parameters are:
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:
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.