Importing/Exporting Data  «Prev 

OPENXML

Many of the concepts you have learned in this module up to this point stray toward what I would call advanced SQL Server topics. OPENXML strays even farther, and thus I will not delve too deep into it here. I want to make sure you understand what it does and some of the situations it can be useful for. Keep in mind that many of the things OPENXML was created for are now handled in a more native way by simply placing your XML into a native XML data type and using the XML type methods discussed earlier in the module. When the original XML feature set was fi rst introduced back in SQL Server 2000, the native XML data type did not yet exist. Developers had FOR XML, and thus significant power for turning relational data into XML, but we needed something to make XML addressable in a relational formal, that something was OPENXML.
OPENXML is a rowset function that opens your string much as other rowset functions (such as OPENQUERY and OPENROWSET) work. This means that you can join to an XML document, or even use it as the source of input data by using an INSERT..SELECT or a SELECT INTO.
The major difference is that it requires you to use a couple of system stored procedures to prepare your document and clear the memory after you are done using it. To set up your document, you use sp_xml_preparedocument. This moves the string into memory and pre-parses it for optimal query performance. The XML document will stay in memory until you explicitly say to remove it or you terminate the connection that sp_xml_preparedocument was called on. The syntax is pretty simple:

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

Select into statement

The list of columns to create in the new table

Specifies the name of the new table

The rest of the select statement can be any valid select statement that does not contain the compute clause.