for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegroups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>|||Thanks Michael for reply
I'm using SQL 2000
Vishy

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy
>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik
|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy
|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegr oups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>
|||Thanks Michael for reply
I'm using SQL 2000
Vishy

FOR XML RAW & Null Characters Bug

Just curious if anyone else has seen this issue:
We had a situation where the XML being retrieved from an XML Explicit query
was malformed. After looking at the resulting XML from the query being ran
with different sets of paramaters we were able to identify that the building
of the XML was stopping on the same value. Looking further we noticed this
field had a null character (ASCII code of 0).
Here's a simple example showing the issue:
declare @.var varchar(100)
set @.var='Josh' + CHAR(0)
select @.var AS Col
for XML RAW
this results in <row Col="Josh
I couldn't find if this was a documented bug or expected behavior? Does
anyone know of any articles that describe how the null character is handled
in SQL?
Thanks
JoshOn Mon, 30 Jan 2006 13:05:31 -0800, joshb wrote:

>Just curious if anyone else has seen this issue:
>We had a situation where the XML being retrieved from an XML Explicit query
>was malformed. After looking at the resulting XML from the query being ran
>with different sets of paramaters we were able to identify that the buildin
g
>of the XML was stopping on the same value. Looking further we noticed this
>field had a null character (ASCII code of 0).
>Here's a simple example showing the issue:
>declare @.var varchar(100)
>set @.var='Josh' + CHAR(0)
>select @.var AS Col
>for XML RAW
>this results in <row Col="Josh
>I couldn't find if this was a documented bug or expected behavior? Does
>anyone know of any articles that describe how the null character is handled
>in SQL?
Hi Josh,
The problem is that ASCII 0 is used as a string delimiter in many
languages. So the low-level code that sends the results to the screen
sees the ASCII 0 and thinks it's the end of the string.
Try running your code from another client (e.g. osql).
Hugo Kornelis, SQL Server MVP|||Hi, Josh
The problem is fixed in SQL Server 2005, where your example returns
this (in Management Studio or in Query Analyzer):
<row Col="Josh�"/>
However, Management Studio warns that the character with the code 0x0
is illegal in XML documents.
Razvan