Can FOR XML PATH create truly _empty_ elements?

#1

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

Now this is causing me some grief because I need to work with an XSD that
enforces that certain elements if present really have no content. When I try
to use the pseudo-empty tags being returned by FOR XML PATH, not only doesn't
it validate but sql server fails catastrophically with a system assertion
failure.

To demonstrate this concretely, create the following trivial schema:

create XML SCHEMA COLLECTION MySchema As
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="tag">
<xs:complexType>
<xs:sequence/>
</xs:complexType>
</xs:element>
</xs:schema>
'

Now try and execute this:

declare @xml xml(MySchema)
set @xml = (select '' as 'tag' for xml path (''), type)

Both sql server 2005 and 2008 fail with:

Location: xmlwriter.cpp:3059
Expression: FALSE
SPID: 52
Process ID: 816
Description: Invalid switch value
Msg 3624, Level 20, State 1, Line 2
A system assertion check has failed. Check the SQL Server error log for
details. Typically, an assertion failure is caused by a software bug or data
corruption. To check for database corruption, consider running DBCC CHECKDB.
If you agreed to send dumps to Microsoft during setup, a mini dump will be
sent to Microsoft. An update might be available from Microsoft in the latest
Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.

So I'm guessing there is a bug in sql server here, but is there a way to
create genuinely _empty_ elements using FOR XML PATH? I'm pretty sure this
would solve my problem.

Any help would be much appreciated!

#2

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#3

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#4

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#5

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#6

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#7

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#8

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#9

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

#10

Lee Marks wrote:

Quote:

I'm having difficulty doing something that should be really simple. I need to
be able to generate empty elements (eg. <tag/>) using FOR XML PATH.

I would have thought that this should work:

declare @xml xml
set @xml = (select '' as 'tag' for xml path (''), type)

This creates "<tag></tag>" rather than "<tag/>". There should be no
difference but in fact the first one has an empty text node in it, as
demonstrated by this query:

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

which returns <yes/>.

This problem doesn't occur if I type the xml literally, ie

set @xml = '<tag></tag>'
or
set @xml = '<tag/>'

both produce genuinely empty elements that will produce <no/> when run
against the xquery above.

I have a partial solution for you:

declare @xml xml
set @xml = (select NULL as 'tag' for xml path (''), type, elements xsinil)

select @xml.query('if (/tag/text()) then <yes/> else <no/>')

select @xml

The result is

<tag xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

where the xsi:nil="true" is the W3C XML schema way of saying the element
has a NULL value.

Thus if you can live with the xsi:nil then selecting NULL with elements
xsinil should give you an empty element.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/