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 AUTO

Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
FOR XML produces XML fragments - not documents. You might need to add a root
element to the results in order to make it well-formed.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
|||Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
directive). You need to do it on the client. The ADO and ADO.NET classes
shipped with SQLXML have a RootTag property you can use, or you could use a
template or schema. Have a look at the documentation shipped with SQLXML
3.0.
Chers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||http://www.microsoft.com/downloads/d...displaylang=en
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||The root property is available in the OLEDB provider that ships with SQL
Server 2000. So it is not part of the server-side software but the client
component.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
> http://www.microsoft.com/downloads/d...displaylang=en
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Is this a separate component of SQL!!!
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> a
> ************************************************** **************************
> ************************************************** **************************
>
>
|||Thanks chaps, I'll give it a try...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23Yv8W1XZEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The root property is available in the OLEDB provider that ships with SQL
> Server 2000. So it is not part of the server-side software but the client
> component.
> Best regards
> Michael
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
ROOT[vbcol=seagreen]
classes[vbcol=seagreen]
use[vbcol=seagreen]
SQLXML[vbcol=seagreen]
but[vbcol=seagreen]
a[vbcol=seagreen]
I[vbcol=seagreen]
************************************************** **************************[vbcol=seagreen]
error[vbcol=seagreen]
-[vbcol=seagreen]
************************************************** **************************
>