Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

FOR XML output results

Hi,

I've a problem with the result of a FOR XML EXPLICIT query. the output is ok but it suddenly stops. it looks like there is a limit on characters or something.

The output is:

<b100><b110>2141287</b110><b120>GB</b120><b130>A1 </b130><b140>1984-12-12T00:00:00</b140></b100><b200><b210>08408789</b210><b210a>1984 1234</b210a><b211>A</b211><b220>1984-04-05T00:00:00</b220><b250>Kat</b250><b260>Kat</b260></b200><b300><b310>05244683</b3 --and here it stops!!

Does anyone know how to solve this problem?

Greetings,
Renehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25662

macka.

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriag
e
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J JustinHello J,
As far as I recall, no, there's no particular issue with this if you're usin
g
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. I checked my data again. You are right. Carriage return is also
stored on couple of rows. If I exclude those rows in the WHERE clause, then
all are working fine. A custom developed web service is using this data.
How to make sure that SQL query with FOR XML statement will return all data
without issues regardless of whether carriage return is present in a row or
not?
J Justin
"Kent Tegels" wrote:

> Hello J,
> As far as I recall, no, there's no particular issue with this if you're us
ing
> a tool that reads it all as byte stream. Are you sure the data in question
> doesn't have a return? How are you reading the data?
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Hello J,
a.) clean up the existing data to remove the returns
b.) enforce good data validation on entry to not allow returns
c.) consider using an FOR XML EXPLICT query to emit the questionable field
as CDATA (I think this is possible)
kt

FOR XML output - strange behavior

We have an application that executes a SQL statement
SELECT * FROM tablename FOR XML AUTO, ELEMENTS and returns the data as XML.
However, we are running into the issue where after 2048 characters a carriage
is inserted in the output and thus the XML ends up not being valid.
Is it a known issue? Is there any workaround?
We have SQL Server 2000 Entp edition with SP4.
Thanks for any input.
J Justin
Hello J,
As far as I recall, no, there's no particular issue with this if you're using
a tool that reads it all as byte stream. Are you sure the data in question
doesn't have a return? How are you reading the data?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

FOR XML Output

How do I get an output from the query below (1) one
record at a time, (2) insert the output one row at a time
into a XMLType column of a table? In addition, how do I
assign the output to a variable before step (2) so that I
can manipulate the output?
select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer')
***Please disregard the new Path feature in Yukon
Thanks,
C TO
I presume you use SQL Server 2005 Express or Beta 2.
If you need to insert each customer info in XML format into a separate row
of another table you can do (I use FOR XML ..., TYPE for FOR XML to generate
XML type directly):
insert into your_table_with_xml_col
select
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
for xml path('Customer'), TYPE
)
FROM Customers
If you want all customers into an XML variable you can write:
declare @.x xml
set @.x=
(select CustomerID as "@.ID",
(select OrderID as "data()"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('')
) as "@.OrderIDs",
CompanyName,
ContactTitle as "ContactName/@.ContactTitle",
ContactName as "ContactName/text()",
PostalCode as "Address/@.ZIP",
Address as "Address/Street",
City as "Address/City"
FROM Customers
FOR XML PATH('Customer'), TYPE
)
Does it answer your question?
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:867101c47843$77008040$a601280a@.phx.gbl...
> How do I get an output from the query below (1) one
> record at a time, (2) insert the output one row at a time
> into a XMLType column of a table? In addition, how do I
> assign the output to a variable before step (2) so that I
> can manipulate the output?
>
<skip/>
> Thanks,
> C TO
|||Dear Eugene,
Beautiful!!!!!
Thanks, thank, thanks!!!
TO

>--Original Message--
>I presume you use SQL Server 2005 Express or Beta 2.
>If you need to insert each customer info in XML format
into a separate row
>of another table you can do (I use FOR XML ..., TYPE for
FOR XML to generate
>XML type directly):
>insert into your_table_with_xml_col
>select
> (select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> for xml path('Customer'), TYPE
> )
>FROM Customers
>If you want all customers into an XML variable you can
write:
>declare @.x xml
>set @.x=
>(select CustomerID as "@.ID",
> (select OrderID as "data()"
> from Orders
> where Customers.CustomerID=Orders.CustomerID
> FOR XML PATH('')
> ) as "@.OrderIDs",
> CompanyName,
> ContactTitle as "ContactName/@.ContactTitle",
> ContactName as "ContactName/text()",
> PostalCode as "Address/@.ZIP",
> Address as "Address/Street",
> City as "Address/City"
> FROM Customers
> FOR XML PATH('Customer'), TYPE
>)
>Does it answer your question?
>Regards,
>Eugene
>--
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>"C TO" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:867101c47843$77008040$a601280a@.phx.gbl...
time[vbcol=seagreen]
I
><skip/>
>
>.
>

For xml into insert

Hi,
For reasons beyond the scope of this question, i need to insert into a
varchar field of a table, the output of a select * from table2 for xml auto,
elements .
I haven't figured out a way to do this. any ideas?
thanksHello Ishmael,

> For reasons beyond the scope of this question, i need to insert into a
> varchar field of a table, the output of a select * from table2 for xml
> auto, elements . I haven't figured out a way to do this. any ideas?
If you are using SQL Server 2000, you'll need to do this via a round trip
due to the way that the XML Aggregator works -- have some client fetch the
XML and then insert it into the target table column. If you are using SQL
Server 2005, it's pretty easy:
insert into schema.tabke values (xmlCol)
select ... for xml auto,elements
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

For xml into insert

Hi,
For reasons beyond the scope of this question, i need to insert into a
varchar field of a table, the output of a select * from table2 for xml auto,
elements .
I haven't figured out a way to do this. any ideas?
thanks
Hello Ishmael,

> For reasons beyond the scope of this question, i need to insert into a
> varchar field of a table, the output of a select * from table2 for xml
> auto, elements . I haven't figured out a way to do this. any ideas?
If you are using SQL Server 2000, you'll need to do this via a round trip
due to the way that the XML Aggregator works -- have some client fetch the
XML and then insert it into the target table column. If you are using SQL
Server 2005, it's pretty easy:
insert into schema.tabke values (xmlCol)
select ... for xml auto,elements
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML EXPLICIT output on MSDE?

Hello,
I have a problem with one of my SPs with a FOR XML EXPLICIT line. I tried it
on a SQL Server 2000 installation and it works fine.
Anyway, I've tried it with some of the MSDE GUIs (both standalone's and
WebServer Based) and it doesn't come up with the output. On the MSDE version
when I run it through the GUI Query Analyzer versions, it seems to run, it
spits out the non XML Select statements and it shows the "rows affected"
messages and it doesn't show any error messages.
Any idea what's happening?
Jeeves
Hi Jeeves,
It just sounds like the tools aren't rendering the XML output. That's
probably not that surprising. The MSDE won't be the problem. It does it just
fine so it will be ok with your apps.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jeeves De Veyra" <me@.jeevester.com> wrote in message
news:%23%23Dh6aDrEHA.2924@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a problem with one of my SPs with a FOR XML EXPLICIT line. I tried
> it
> on a SQL Server 2000 installation and it works fine.
> Anyway, I've tried it with some of the MSDE GUIs (both standalone's and
> WebServer Based) and it doesn't come up with the output. On the MSDE
> version
> when I run it through the GUI Query Analyzer versions, it seems to run, it
> spits out the non XML Select statements and it shows the "rows affected"
> messages and it doesn't show any error messages.
> Any idea what's happening?
> Jeeves
>
|||Would you know of an MSDE tool that renders XML properly. I've been through
all of em and they give me zip... (waaahhhh!!!!)
Incidentally, I tried Microsofts Web based tool (the one that comes with
Cassini), how can the script timeouts be changed?
Jeeves
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:uxeJ29ErEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hi Jeeves,
> It just sounds like the tools aren't rendering the XML output. That's
> probably not that surprising. The MSDE won't be the problem. It does it
just[vbcol=seagreen]
> fine so it will be ok with your apps.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Jeeves De Veyra" <me@.jeevester.com> wrote in message
> news:%23%23Dh6aDrEHA.2924@.TK2MSFTNGP12.phx.gbl...
tried[vbcol=seagreen]
it
>

FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!

Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.

<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...

</Customer>
</Customers>

Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!

My apologies for the delay in answering....

If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...

Here is the EXPLICIT solution:

select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"

union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers

-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit

and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):

select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')

Best regards
Michael

FOR XML EXPLICIT - Empty Tags?

Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty
]
for xml explicit
Cheers,
AndrewYou can do it by adding another tag (and therefore another UNION) to your
query in which the empty "container" element is craeted by selecting NULL as
shown in the following example from Northwind.
An alternative approach would be to use an annotated schema with a
sql:is-constant annotation.
cheers,
Graeme
sample code
--
Use Northwind
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Invoices!1],
NULL AS [Invoice!2!InvoiceNo],
NULL AS [Invoice!2!Date],
NULL AS [Item!3!Product],
NULL AS [Item!3!Price!element]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
OrderID,
OrderDate,
NULL,
NULL
FROM Orders
UNION ALL
SELECT 3,
2,
NULL,
O.OrderID,
NULL,
P.ProductName,
OD.UnitPrice
FROM Orders O JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN Products P
ON OD.ProductID = P.ProductID
ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
FOR XML EXPLICIT
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Bunce" <Bunce@.discussions.microsoft.com> wrote in message
news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty
]
for xml explicit
Cheers,
Andrew|||Note that this becomes much easier in SQL Server 2005 (see
http://msdn.microsoft.com/library/e...l/forxml2k5.asp). Note
that I find such wrapper elements to be of questionable value when they only
provide an additional level of indirection in your tree. Since XPath has
list semantics, /Invoice already gives you all invoices. If you add
additional properties to Invoices such as summary information, it makes
sense.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OQmoBJtlFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can do it by adding another tag (and therefore another UNION) to your
> query in which the empty "container" element is craeted by selecting NULL
> as
> shown in the following example from Northwind.
> An alternative approach would be to use an annotated schema with a
> sql:is-constant annotation.
> cheers,
> Graeme
> sample code
> --
> Use Northwind
> SELECT 1 AS Tag,
> NULL AS Parent,
> NULL AS [Invoices!1],
> NULL AS [Invoice!2!InvoiceNo],
> NULL AS [Invoice!2!Date],
> NULL AS [Item!3!Product],
> NULL AS [Item!3!Price!element]
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> NULL,
> OrderID,
> OrderDate,
> NULL,
> NULL
> FROM Orders
> UNION ALL
> SELECT 3,
> 2,
> NULL,
> O.OrderID,
> NULL,
> P.ProductName,
> OD.UnitPrice
> FROM Orders O JOIN [Order Details] OD
> ON O.OrderID = OD.OrderID
> JOIN Products P
> ON OD.ProductID = P.ProductID
> ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
> FOR XML EXPLICIT
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Bunce" <Bunce@.discussions.microsoft.com> wrote in message
> news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
> Hey all.
> I need to output a query in a given XML format and I figure I'd have a
> stab
> using the FOR XML clause of SQL 2000 first rather than in code.
> I've worked out the basics using the FOR XML EXPLICIT clause and am able
> to
> output a structure such as this:
> <state id="SA">
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </state>
> <state id="NSW">
> <property id="24">
> ...
> </state>
>
> However I haven't worked out how to add 'empty' surrounding tags (I think
> they may be called 'associations' in XML speak), so the schema would
> become:
> (note the addition of the <states> and <properties> tags)
> <states>
> <state id="SA">
> <properties>
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </properties>
> </state>
> <state id="NSW">
> <properties>
> <property id="24">
> ...
> </properties>
> </state>
> </states>
> (apologies if the formatting doesn't stick)
> Any ideas? Anyone familiar with using XML in SS?
> For reference, my actual current query is below - I figured the above
> example was easier to use.
> SELECT
> 1 as Tag,
> NULL as Parent,
> c.textstate as [state!1!idstate],
> null as [property!2!name!element],
> null as [property!2!areaHA!element],
> null as [property!2!dateGranted!element],
> null as
> [property!2!titleHoldingBody!element],
> null as [property!2!idproperty]
> FROM
> dbo.tblStates c
> Where
> c.IDState<>0
> union all
> SELECT
> 2,
> 1,
> b.textstate,
> a.ShortLandName,
> a.area,
> a.GrantDate,
> a.THBName,
> a.IDProperty
> FROM
> dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
> a.IDState = b.IDState
> Order By
> [state!1!idstate],[property!2!idproperty
]
> for xml explicit
> Cheers,
> Andrew
>

FOR XML EXPLICIT - Empty Tags?

Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstateas [state!1!idstate],
nullas [property!2!name!element],
nullas [property!2!areaHA!element],
nullas [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty]
for xml explicit
Cheers,
Andrew
You can do it by adding another tag (and therefore another UNION) to your
query in which the empty "container" element is craeted by selecting NULL as
shown in the following example from Northwind.
An alternative approach would be to use an annotated schema with a
sql:is-constant annotation.
cheers,
Graeme
sample code
Use Northwind
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Invoices!1],
NULL AS [Invoice!2!InvoiceNo],
NULL AS [Invoice!2!Date],
NULL AS [Item!3!Product],
NULL AS [Item!3!Price!element]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
OrderID,
OrderDate,
NULL,
NULL
FROM Orders
UNION ALL
SELECT 3,
2,
NULL,
O.OrderID,
NULL,
P.ProductName,
OD.UnitPrice
FROM Orders O JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN Products P
ON OD.ProductID = P.ProductID
ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
FOR XML EXPLICIT
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Bunce" <Bunce@.discussions.microsoft.com> wrote in message
news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
....
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
....
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty]
for xml explicit
Cheers,
Andrew
|||Note that this becomes much easier in SQL Server 2005 (see
http://msdn.microsoft.com/library/en.../forxml2k5.asp). Note
that I find such wrapper elements to be of questionable value when they only
provide an additional level of indirection in your tree. Since XPath has
list semantics, /Invoice already gives you all invoices. If you add
additional properties to Invoices such as summary information, it makes
sense.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OQmoBJtlFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can do it by adding another tag (and therefore another UNION) to your
> query in which the empty "container" element is craeted by selecting NULL
> as
> shown in the following example from Northwind.
> An alternative approach would be to use an annotated schema with a
> sql:is-constant annotation.
> cheers,
> Graeme
> sample code
> --
> Use Northwind
> SELECT 1 AS Tag,
> NULL AS Parent,
> NULL AS [Invoices!1],
> NULL AS [Invoice!2!InvoiceNo],
> NULL AS [Invoice!2!Date],
> NULL AS [Item!3!Product],
> NULL AS [Item!3!Price!element]
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> NULL,
> OrderID,
> OrderDate,
> NULL,
> NULL
> FROM Orders
> UNION ALL
> SELECT 3,
> 2,
> NULL,
> O.OrderID,
> NULL,
> P.ProductName,
> OD.UnitPrice
> FROM Orders O JOIN [Order Details] OD
> ON O.OrderID = OD.OrderID
> JOIN Products P
> ON OD.ProductID = P.ProductID
> ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
> FOR XML EXPLICIT
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Bunce" <Bunce@.discussions.microsoft.com> wrote in message
> news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
> Hey all.
> I need to output a query in a given XML format and I figure I'd have a
> stab
> using the FOR XML clause of SQL 2000 first rather than in code.
> I've worked out the basics using the FOR XML EXPLICIT clause and am able
> to
> output a structure such as this:
> <state id="SA">
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </state>
> <state id="NSW">
> <property id="24">
> ...
> </state>
>
> However I haven't worked out how to add 'empty' surrounding tags (I think
> they may be called 'associations' in XML speak), so the schema would
> become:
> (note the addition of the <states> and <properties> tags)
> <states>
> <state id="SA">
> <properties>
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </properties>
> </state>
> <state id="NSW">
> <properties>
> <property id="24">
> ...
> </properties>
> </state>
> </states>
> (apologies if the formatting doesn't stick)
> Any ideas? Anyone familiar with using XML in SS?
> For reference, my actual current query is below - I figured the above
> example was easier to use.
> SELECT
> 1 as Tag,
> NULL as Parent,
> c.textstate as [state!1!idstate],
> null as [property!2!name!element],
> null as [property!2!areaHA!element],
> null as [property!2!dateGranted!element],
> null as
> [property!2!titleHoldingBody!element],
> null as [property!2!idproperty]
> FROM
> dbo.tblStates c
> Where
> c.IDState<>0
> union all
> SELECT
> 2,
> 1,
> b.textstate,
> a.ShortLandName,
> a.area,
> a.GrantDate,
> a.THBName,
> a.IDProperty
> FROM
> dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
> a.IDState = b.IDState
> Order By
> [state!1!idstate],[property!2!idproperty]
> for xml explicit
> Cheers,
> Andrew
>

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated..."NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated...
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell
|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated..."NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...

FOR XML AUTO, Elements and blank cell

When doing a basic select one of the SQL Server 2000 sample databasaes
with "FOR XML AUTO, Elements" the output is not well formed. Nothing
can parse it. Sometimes if there is no data in the cell it will have
an opening tag like <city> but no closing tag. Other times it will
completly eliminate data if there is nothing in it so the different
nodes in the xml all have different sets of data. How do you get SQL
Server to output the query well formed and have all the cell data in
each row in each node regardless of if it's blank or not?
Thanks.
JR"JR" <jriker1@.yahoo.com> wrote in message
news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> When doing a basic select one of the SQL Server 2000 sample databasaes
> with "FOR XML AUTO, Elements" the output is not well formed. Nothing
> can parse it. Sometimes if there is no data in the cell it will have
> an opening tag like <city> but no closing tag. Other times it will
> completly eliminate data if there is nothing in it so the different
> nodes in the xml all have different sets of data. How do you get SQL
> Server to output the query well formed and have all the cell data in
> each row in each node regardless of if it's blank or not?
> Thanks.
> JR
>
Can you post the query?
Joe Fawcett - XML MVP
[url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/ur
l]|||First FOR XML results are never guaranteed to be fully well-formed (they can
have multiple top-level nodes). You can add a root node by setting the Root
name property on your command stream object to get the result wrapped into a
document.
Secondly, you need to use the correct API to get the XML back as a stream:
The ICommandStream object in ADO or the correct API in ADO.Net to get the
XML back as a stream and not just the first 2k block...
The documentation has samples that should help you further...
Best regards
Michael
"Joe Fawcett" <joefawcett@.newsgroup.nospam> wrote in message
news:%23QJCINERGHA.2088@.TK2MSFTNGP14.phx.gbl...
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1141948738.843007.54400@.e56g2000cwe.googlegroups.com...
> Can you post the query?
> --
> Joe Fawcett - XML MVP
> [url]https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5[/
url]
>

FOR XML AUTO, Elements and blank cell

When doing a basic select one of the SQL Server 2000 sample databasaes
with "FOR XML AUTO, Elements" the output is not well formed. Nothing
can parse it. Sometimes if there is no data in the cell it will have
an opening tag like <city> but no closing tag. Other times it will
completly eliminate data if there is nothing in it so the different
nodes in the xml all have different sets of data. How do you get SQL
Server to output the query well formed and have all the cell data in
each row in each node regardless of if it's blank or not?
Thanks.
JR
"JR" <jriker1@.yahoo.com> wrote in message
news:1141948738.843007.54400@.e56g2000cwe.googlegro ups.com...
> When doing a basic select one of the SQL Server 2000 sample databasaes
> with "FOR XML AUTO, Elements" the output is not well formed. Nothing
> can parse it. Sometimes if there is no data in the cell it will have
> an opening tag like <city> but no closing tag. Other times it will
> completly eliminate data if there is nothing in it so the different
> nodes in the xml all have different sets of data. How do you get SQL
> Server to output the query well formed and have all the cell data in
> each row in each node regardless of if it's blank or not?
> Thanks.
> JR
>
Can you post the query?
Joe Fawcett - XML MVP
https://mvp.support.microsoft.com/pr...8-8741D22D17A5
|||First FOR XML results are never guaranteed to be fully well-formed (they can
have multiple top-level nodes). You can add a root node by setting the Root
name property on your command stream object to get the result wrapped into a
document.
Secondly, you need to use the correct API to get the XML back as a stream:
The ICommandStream object in ADO or the correct API in ADO.Net to get the
XML back as a stream and not just the first 2k block...
The documentation has samples that should help you further...
Best regards
Michael
"Joe Fawcett" <joefawcett@.newsgroup.nospam> wrote in message
news:%23QJCINERGHA.2088@.TK2MSFTNGP14.phx.gbl...
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1141948738.843007.54400@.e56g2000cwe.googlegro ups.com...
> Can you post the query?
> --
> Joe Fawcett - XML MVP
> https://mvp.support.microsoft.com/pr...8-8741D22D17A5
>

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]
************************************************** **************************
>