OPENXML with Namespaces

When I need to perform a large update or insert in of records in a web application, I almost always send it to SQL Server as XML string, have it parse my XML and do its magic. This way you avoid lots and lots of round trips to the database just to insert one item at a time.

XML support in SQL Server 2000 (SQL2K) is a bolt-on, while in the upcoming SQL Server 2005 (SQL2K5) it is promised to be "native." XML parsing and other handling is done with the help of two system stored procedures: sp_xml_preparedocument and sp_xml_removedocument. If you are new to this, check out this subject in Books Online (which are also installed with SQL Server so don’t let the "online" part fool you).

The simplistic and strictly hypothetic code below selects the UserId, FirstName and LastName elements from an XML feed. The syntax may seem a bit verbose, which is often the case with XML, but you get used to it fast.

DECLARE @idoc int
DECLARE @doc nvarchar (1000)

SET @doc = N'
<?xml version="1.0" encoding="UTF-16"?>
<Users>
 <User>
   <UserId>100</UserId>
   <FirstName>John</FirstName>
   <LastName>Doe</LastName>
 </User>
 <User>
   <UserId>105</UserId>
   <FirstName>Jane</FirstName>
   <LastName>Doe</LastName>
 </User>
</Users>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT UserId, FirstName, LastName
FROM   OPENXML (@idoc, '/Users/User')
       WITH (UserId int 'UserId', 
             FirstName nvarchar (80) 'FirstName', 
             LastName nvarchar(80) 'LastName')

EXEC sp_xml_removedocument @idoc

Note the encoding: encoding="UTF-16". If you happen to serialize a custom collection or a DataSet into XML you will probably end up with it against your will.

It gets more interesting if you have a namespace in your XML—something I recommend that you always specify. Books Online aren’t crystal clear on how the syntax changes in the face of a namespace, so here it is:

DECLARE @idoc int
DECLARE @doc nvarchar (1000)

SET @doc = N'
<?xml version="1.0" encoding="UTF-16"?>
<Users xmlns="urn:xml-with-namespace">
 <User>
   <UserId>100</UserId>
   <FirstName>John</FirstName>
   <LastName>Doe</LastName>
 </User>
 <User>
   <UserId>105</UserId>
   <FirstName>Jane</FirstName>
   <LastName>Doe</LastName>
 </User>
</Users>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, 
     '<root xmlns:myns="urn:xml-with-namespace" />'

SELECT UserId, FirstName, LastName
FROM   OPENXML (@idoc, '/myns:Users/myns:User')
       WITH (UserId int 'myns:UserId', 
             FirstName nvarchar (80) 'myns:FirstName', 
             LastName nvarchar(80) 'myns:LastName')

EXEC sp_xml_removedocument @idoc

The call to sp_xml_preparedocument has an additional parameter: '<root xmlns:myns="urn:xml-with-namespace" />'. Also, note how OPENXML changed: every field has a corresponding namespace prefix. You can substitute myns with any other prefix as long as you apply it consistently in OPENXML... WITH.

Happy hacking!