XML leading spaces problem solved

[Ellen K.]

In a previous post I mentioned having a problem getting leading spaces
to be written to SQL Server using OPENXML. They showed up fine in my
XML documents if I printed them, but not in my database.

(Our legacy flat-file data uses fixed-length character expressions for
data like contract numbers, I guess because in a 16-bit environment
you'd run out of available numbers REALLY fast... right now our
contract numbers are in the 700,000's, so if I want the new and old
systems to talk to each other I need to preserve the four leading
spaces.)

Blabla, the answer turns out to be:
1. In your XML document, make the column values entities, not
attributes.
2. In your XML document, make the values with leading spaces CDATA.
3. In your stored procedure, set the OPENXML flag to 2.

It probably also doesn't hurt to use
xml:space="preserve"
although by itself that didn't fix the problem.

Hope this helps someone else. :)