retrieving XML issues - SQL 2000

In my SQL Server 2000 database, I need to return several records
containing xml as a single xml.
This almost gives me what I want:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xmltext]
from XMLTable
for xml explicit
The trouble is that each record includes an xml declaration tag:
<?xml version="1.0" encoding="UTF-8"?>
So, a sample record might look something like:
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
And I want this returned:
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
Instead I'm getting this:
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
<XMLDataRoot version="1.0" encoding="UTF-8">
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
...
This, of course, is not well-formed xml and results in error as soon as
my app tries to parse it. (I also tried changing the nodename in my
query to differ from the root node name in the record, but still, it
returns not well-formed xml)
If I change my query to use the xml directive instead of xmltext like
this:
select
1 tag,
null parent,
XMLData [XMLDataRoot!1!!xml]
from XMLTable
for xml explicit
I get this:
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
<XMLDataRoot>
<?xml version="1.0" encoding="UTF-8"?>
<XMLDataRoot>
<foo>
<bar/>
</foo>
</XMLDataRoot>
</XMLDataRoot>
...
This also results in error as soon as my app tries to parse it: "The XML
Declaration is Unexpected"
I can't strip the xml declaration out using string functions, as the xml
is stored in a text field. For another stored procedure I want to
return just a single record, and the xml declaration is desired for
that.
How can I return the xml without the xml declarations fouling up my
results?
thanks
-ivan.
Two options:
Use string functions to drop the XML declarations (may need more complex
coding because of TEXT column).
Use SQL Server 2005 and cast/alter the column to an XML data type (you still
may have some issues with the UTF-8 encoding depending on your column code
page, then first cast it to varbinary(max) before casting it to XML).
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns97637C2EC4F26newsNOSPAMgilly3com@.207.46.24 8.16...
> In my SQL Server 2000 database, I need to return several records
> containing xml as a single xml.
> This almost gives me what I want:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xmltext]
> from XMLTable
> for xml explicit
> The trouble is that each record includes an xml declaration tag:
> <?xml version="1.0" encoding="UTF-8"?>
> So, a sample record might look something like:
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> And I want this returned:
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> Instead I'm getting this:
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> <XMLDataRoot version="1.0" encoding="UTF-8">
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> ...
> This, of course, is not well-formed xml and results in error as soon as
> my app tries to parse it. (I also tried changing the nodename in my
> query to differ from the root node name in the record, but still, it
> returns not well-formed xml)
> If I change my query to use the xml directive instead of xmltext like
> this:
> select
> 1 tag,
> null parent,
> XMLData [XMLDataRoot!1!!xml]
> from XMLTable
> for xml explicit
> I get this:
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> <XMLDataRoot>
> <?xml version="1.0" encoding="UTF-8"?>
> <XMLDataRoot>
> <foo>
> <bar/>
> </foo>
> </XMLDataRoot>
> </XMLDataRoot>
> ...
> This also results in error as soon as my app tries to parse it: "The XML
> Declaration is Unexpected"
> I can't strip the xml declaration out using string functions, as the xml
> is stored in a text field. For another stored procedure I want to
> return just a single record, and the xml declaration is desired for
> that.
> How can I return the xml without the xml declarations fouling up my
> results?
> thanks
> -ivan.
sql