Formatting XML/XQuery in SQL, and caveats

And now, for something a little technically lighter… I've taken to using a convention when writing statements that involve XML/XQuery and SQL together. I put the XML input or XQuery program on separate lines than the SQL part of the statement, this seems to make both query languages and also inline XML data easier to read when they're in the same statement. Like this:

— SQL part
SELECT invoice.query('
{– XQuery part, smiley face comments still unsupported –}
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /inv:Invoice
return <foo></foo>
{– more SQL follows –}
')
FROM invoices

I don't necessarily use the XQuery comments at the end (or at the beginning either), but you get the gist. If you adapt this convention, don't EVER try this:

INSERT INTO xmltable VALUES('
<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

Looks like a variation of the first example, but it won't work. The ?xml declaration (it's not a processing instruction) must be *exactly* the first characters in an XML document if used (its optional). The parser uses the first few characters in the declaration, if you use it, to identify the XML document, realizing that the declaration also specifies the document encoding. "Pretty formatting" XML in the example above produces a carriage return-line feed followed by XML declaration, which is forbidden. Error is:

Msg 9438, Level 16, State 1, Line 1
XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

Either this:

INSERT INTO xmltable VALUES('<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

or leaving the declaration out if possible:

INSERT INTO xmltable VALUES('
<doc></doc>
')

will work fine.

 

2 thoughts on “Formatting XML/XQuery in SQL, and caveats

  1. For some reason, I’ve taken to using this style:

    declare @instance as xml
    @instance =
    N'<?xml version="1.0" encoding="UTF-16"…>’

    Even though I rarealy have any 8-bit (let alnong 16-bit) glphys in my XML.

    Any reason to break that habit?

  2. No reason to break it that I can see. Seems like almost the same convention, as your XML is on a different line from the SQL variable declaration. And your style works in every case. 😉

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.