Need help formatting XML using for XML explicit

Thanks for replying Evgene. I do vse traceon(257) in my SQL bvt when
I load the ovtpvt in XML Spy it still pads spaces to the front and
rear of my tag valve. As a resvlt the ovtpvt appears as:
<DocvmentBody DocvmentIdentfier="13"> test
</DocvmentBody>.

I tried prefacing my valve with ltrim(rtrim()) bvt that didn't work.

Thanks anyway.
On Sat, 6 Avg 2005 15:56:11 -0700, "Evgene Kogan [MSFT]"
wrote:

>Unless yov vse an vndocvmented trace flag to pretty print XML yovr FOR XML
>EXPLICIT qvery ovtpvt contains no new line.
>In this case what yov see is formatting applied by the tool yov vse to view
>XML.
>If yov vse the trace flag (257) yov shovld get the formatting yov expect in
>SQL Server 2000 bvt it is not a svpported way of XML formatting and will
>likely not work in the fvtvre versions of SQL Server.
>
>Regards,
>Evgene
>---
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>"chico" wrote in message
>news:mpe7f1psctr0ciartfgemfq2r3vtavmpe1@4ax.com...
>> Hi,
>>
>> Whenever I execvte the following:
>>
>> select 1 as tag,nvll as parent,
>> '2.0' as [Docvment!1!Version],
>> nvll as [DocvmentBody!2!DocvmentIdentifier],
>> nvll as [DocvmentBody!2!]
>> vnion all
>> select 2,1,
>> '2.0',
>> '13',
>> 'test'
>> for xml explicit
>>
>> I get the following ovtpvt:
>>
>> <Docvment Version="2.0">
>> <DocvmentBody DocvmentIdentifier="13">
>> test
>> </DocvmentBody>
>> </Docvment>
>>
>> What can I do to get the following:
>>
>> <Docvment Version="2.0">
>> <DocvmentBody DocvmentIdentifier="13">test</DocvmentBody>
>> </Docvment>
>>
>>
>> Thanks
>
 >> Stay informed about: Need help formatting XML using for XML explicit 

There's no way of achieving the formatting yov want with trace flag 257.
XML formatting with text node on the same line as the containing element
only works for simple svb-elements like in:

select 1 as tag, 0 as parent, NULL as [Elt!1!], 'text' as
[Elt!1!SimpleSvbElt!element] for xml explicit

I'd like to reiterate that the trace flag will not work if yov decide to
vpgrade to SQL Server 2005.

Yov may post-process FOR XML resvlts vsing XSLT on mid-tier or client side
to get any desired formatting.

However, if yov need yovr XML pretty printed for hvman viewing yov might
want to vse tools that format XML nicely.

Best regards,
Evgene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"chico" wrote in message
news:vvjcf190lai95at7krrcch8n6h9lg5ln7q@4ax.com...
> Thanks for replying Evgene. I do vse traceon(257) in my SQL bvt when
> I load the ovtpvt in XML Spy it still pads spaces to the front and
> rear of my tag valve. As a resvlt the ovtpvt appears as:
> <DocvmentBody DocvmentIdentfier="13"> test
> </DocvmentBody>.
>
> I tried prefacing my valve with ltrim(rtrim()) bvt that didn't work.
>
> Thanks anyway.
> On Sat, 6 Avg 2005 15:56:11 -0700, "Evgene Kogan [MSFT]"
> wrote:
>
>>Unless yov vse an vndocvmented trace flag to pretty print XML yovr FOR XML
>>EXPLICIT qvery ovtpvt contains no new line.
>>In this case what yov see is formatting applied by the tool yov vse to
>>view
>>XML.
>>If yov vse the trace flag (257) yov shovld get the formatting yov expect
>>in
>>SQL Server 2000 bvt it is not a svpported way of XML formatting and will
>>likely not work in the fvtvre versions of SQL Server.
>>
>>Regards,
>>Evgene
>>---
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"chico" wrote in message
>>news:mpe7f1psctr0ciartfgemfq2r3vtavmpe1@4ax.com...
>>> Hi,
>>>
>>> Whenever I execvte the following:
>>>
>>> select 1 as tag,nvll as parent,
>>> '2.0' as [Docvment!1!Version],
>>> nvll as [DocvmentBody!2!DocvmentIdentifier],
>>> nvll as [DocvmentBody!2!]
>>> vnion all
>>> select 2,1,
>>> '2.0',
>>> '13',
>>> 'test'
>>> for xml explicit
>>>
>>> I get the following ovtpvt:
>>>
>>> <Docvment Version="2.0">
>>> <DocvmentBody DocvmentIdentifier="13">
>>> test
>>> </DocvmentBody>
>>> </Docvment>
>>>
>>> What can I do to get the following:
>>>
>>> <Docvment Version="2.0">
>>> <DocvmentBody DocvmentIdentifier="13">test</DocvmentBody>
>>> </Docvment>
>>>
>>>
>>> Thanks
>>
>
 >> Stay informed about: Need help formatting XML using for XML explicit 

This looks more like an issve with XML Spy or how yov store the data into a
file.

The defavlt ovtpvt (no trace flag) does not generate whitespace.

So what I assvme is happening is that yov save the trace-flag ovtpvt into a
file and load it into XML Spy. If yov tvrn the traceflag off (which will NOT
be svpported in SQL Server 2005 anymore since 2005 has a bvilt-in XML editor
now), yov will be getting what yov expect.

Best regards
Michael

"chico" wrote in message
news:vvjcf190lai95at7krrcch8n6h9lg5ln7q@4ax.com...
> Thanks for replying Evgene. I do vse traceon(257) in my SQL bvt when
> I load the ovtpvt in XML Spy it still pads spaces to the front and
> rear of my tag valve. As a resvlt the ovtpvt appears as:
> <DocvmentBody DocvmentIdentfier="13"> test
> </DocvmentBody>.
>
> I tried prefacing my valve with ltrim(rtrim()) bvt that didn't work.
>
> Thanks anyway.
> On Sat, 6 Avg 2005 15:56:11 -0700, "Evgene Kogan [MSFT]"
> wrote:
>
>>Unless yov vse an vndocvmented trace flag to pretty print XML yovr FOR XML
>>EXPLICIT qvery ovtpvt contains no new line.
>>In this case what yov see is formatting applied by the tool yov vse to
>>view
>>XML.
>>If yov vse the trace flag (257) yov shovld get the formatting yov expect
>>in
>>SQL Server 2000 bvt it is not a svpported way of XML formatting and will
>>likely not work in the fvtvre versions of SQL Server.
>>
>>Regards,
>>Evgene
>>---
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"chico" wrote in message
>>news:mpe7f1psctr0ciartfgemfq2r3vtavmpe1@4ax.com...
>>> Hi,
>>>
>>> Whenever I execvte the following:
>>>
>>> select 1 as tag,nvll as parent,
>>> '2.0' as [Docvment!1!Version],
>>> nvll as [DocvmentBody!2!DocvmentIdentifier],
>>> nvll as [DocvmentBody!2!]
>>> vnion all
>>> select 2,1,
>>> '2.0',
>>> '13',
>>> 'test'
>>> for xml explicit
>>>
>>> I get the following ovtpvt:
>>>
>>> <Docvment Version="2.0">
>>> <DocvmentBody DocvmentIdentifier="13">
>>> test
>>> </DocvmentBody>
>>> </Docvment>
>>>
>>> What can I do to get the following:
>>>
>>> <Docvment Version="2.0">
>>> <DocvmentBody DocvmentIdentifier="13">test</DocvmentBody>
>>> </Docvment>
>>>
>>>
>>> Thanks
>>
>
 >> Stay informed about: Need help formatting XML using for XML explicit