dbTalk Databases Forums  

Can FOR XML PATH create truly _empty_ elements?

microsoft.public.sqlserver.xml microsoft.public.sqlserver.xml


Discuss Can FOR XML PATH create truly _empty_ elements? in the microsoft.public.sqlserver.xml forum.

Reply
 
Thread Tools Display Modes
  #1  
Old   
Lee Marks
 
Posts: n/a

Default Can FOR XML PATH create truly _empty_ elements? - 12-04-2008 , 06:31 PM






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!




Reply With Quote
  #2  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM






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/


Reply With Quote
  #3  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #4  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #5  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #6  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #7  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #8  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #9  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM



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/


Reply With Quote
  #10  
Old   
Martin Honnen
 
Posts: n/a

Default Re: Can FOR XML PATH create truly _empty_ elements? - 12-05-2008 , 06:54 AM






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/


Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.