Editor's Note: Send your XML questions to Rich Rollman at xmlquestions@sqlmag.com.
Suppose you have an XML document that contains data you need to load into SQL Server. Do you use XML Bulk Load to import the data, or do you write a stored procedure that uses OpenXML to load the data?
This month, I outline some factors you need to consider when you're making these kinds of decisions and show you how to efficiently use OpenXML to load XML from a file into SQL Server. In addition, a question about how to constrain an attribute's value opens the subject of XML Schemas, which is soon to be a very hot topic. The World Wide Web Consortium (W3C) expects to approve XML Schemas as a W3C recommendation by the time you read this column.
I have an XML document in a file, and I need to extract information from that document and store the information in several related tables in my SQL Server database. How do I import the data from my XML document?
The XML features in SQL Server 2000 provide two possible answers to this question: You can use XML Bulk Load, which is part of XML for SQL Server 2000 Web Release 1, or you can use OpenXML. Your choice depends on several factors. If the XML document has an unstructured format (i.e., it contains interleaved markup, an irregular format, or a mix of formats in an aggregated document), you might prefer to use OpenXML.
In conjunction with the capabilities of the T-SQL language in a stored procedure, OpenXML provides greater flexibility than XML Bulk Load, which requires that you build a mapping schema for unstructured XML documents—a real challenge. However, if the XML document is large enough (i.e., greater than 50KB), XML Bulk Load is probably a better solution because it provides optimization for loading large documents.
Another factor that you should consider is how frequently anyone will use the code you develop. You should weigh the cost of development, the ease of implementation, and the developer's familiarity with specific coding methodologies (e.g., T-SQL versus XML-Data Schemas) against runtime performance. XML Bulk Load requires more XML knowledge than OpenXML requires to construct the mapping schema. OpenXML leverages T-SQL programming, treating the XML document link as a rowset against which you can run SQL queries.
Unfortunately, no magic algorithm determines the correct choice for you. You should carefully consider each factor in relation to the project and the skill set of the person implementing the solution.
In my June 2001 column, I showed you how to use XML Bulk Load to load data into SQL Server. Now, let's use OpenXML and ADO to pass an XML document to a stored procedure that loads the data into SQL Server.
A T-SQL stored procedure must use OpenXML instead of XML Bulk Load because T-SQL can't load a file into a string or stored procedure. Generally, a stored procedure that uses XML receives an XML document as a string. The stored procedure then parses the XML document and runs one or more OpenXML queries on the document to perform the required task. The trick to calling the stored procedure lies in how you pass the XML document as a parameter.
In my February 2001 column, I showed you how to use OpenXML. But I assumed that you already had the XML document in a string variable, so I didn't delve into the details of how to call the stored procedure.
The most straightforward approach you can take to passing an XML document as a parameter is to allocate a huge string to hold the file's contents—obviously inefficient. In addition, writing code that inadvertently copies parts of a file or the entire file multiple times while building a string and passing it to SQL Server is easy to do.
Prev. page
[1]
2
next page 
