LP on .NET

March 9, 2013

OPENXML and Leading/Trailing Spaces

Filed under: SQL Server — Larry Parker @ 6:15 pm

SQL Server’s XML support can be a handy way to extract data out of a database and insert it into another database.  But there’s one thing you need to watch out for.  OPENXML “fixes” up your data by trimming leading and trailing spaces.  I suppose this could be viewed as helpful (e.g. an email address was mistakenly entered with a leading space), but it could have nasty side effects when your customer’s data really needs those spaces.

For example, suppose we have a table containing messages that will be displayed on a signboard outside a restaurant and we need to move the data from one database to another.  Also, assume that our application uses FOR XML to extract the data from the source database, and then uses OPENXML to insert it into the target database.

   1: DECLARE @messageData xml


   3: -- This data can come from anywhere, but say it's extracted out of a database using FOR XML RAW:

   4: --      SELECT MessageGroupId, MessageId, MessageText

   5: --      FROM MessageLine

   6: --      FOR XML RAW, ROOT('Table')

   7: SET @messageData = '<Table Name="dbo.Message">

   8:   <row MessageGroupId="100" MessageId="1" MessageText="Welcome to Joe''s Restaurant!" />

   9:   <row MessageGroupId="100" MessageId="2" MessageText="     *** Early bird specials every night ***" />

  10:   <row MessageGroupId="100" MessageId="3" MessageText="Specials include:" />

  11:   <row MessageGroupId="100" MessageId="4" MessageText="     Meatloaf" />

  12:   <row MessageGroupId="100" MessageId="5" MessageText="     Sirloin Steak" />

  13:   <row MessageGroupId="100" MessageId="6" MessageText="     Baked Chicken" />

  14: </Table>

  15: '


  17: DECLARE @docHandle int


  19: EXEC sp_xml_preparedocument @docHandle OUTPUT, @messageData


  21: -- And now we want to insert the message data into another database, for example:

  22: INSERT INTO MessageLine

  23: (MessageGroupId, MessageId, MessageText)

  24: SELECT MessageGroupId, MessageId, MessageText

  25: FROM OPENXML(@docHandle, N'/Table/row')

  26: WITH (MessageGroupId Int, MessageId Int, MessageText NVarChar(100)) AS x


  28: EXEC sp_xml_removedocument @docHandle; 

The messages get inserted into the target database, but they look like this:


When displayed on the sign outside our restaurant, the meatloaf, sirloin steak and baked chicken all run over to the left.  Not good for business.  Smile

A solution is to use SQL Server’s nodes() method instead of OPENXML.

   1: -- Insert using nodes() method instead of OPENXML

   2: INSERT INTO MessageLine

   3: (MessageGroupId, MessageId, MessageText)

   4: SELECT T.m.value('@MessageGroupId', 'Int') AS MessageGroupId,

   5:        T.m.value('@MessageId', 'Int') AS MessageId,

   6:        T.m.value('@MessageText', 'NVarChar(100)') AS MessageText

   7: FROM @messageData.nodes('/Table/row') AS T(m)

Now our sign looks just the way the customer wants:


The nodes() method preserves the leading and trailing spaces, and is a bit easier to use than OPENXML as well because you don’t have to mess with preparing and removing the @docHandle variable.

There are a couple of caveats to this.  In the value() method, be sure to map the TEXT data type to VARCHAR(MAX), NTEXT to NVARCHAR(MAX), and IMAGE to VARBINARY(MAX).  Also, when copying XML columns, you need to use the query() method instead of value().

So a little extra work in that respect, but the nodes() method will do a better job of copying your data verbatim than OPENXML.

Hope this helps.

Blog at WordPress.com.