FOR XML AUTO in SQL SERVER 2000

Hi

I am trying to use FOR XML AUTO clause in Sql Server 2000.

My requirement is to use this clause and assign the result set into a parameter.Like

Declare @.ss XML

set @.ss=(SELECT * FROM TB_TMGroup WHERE TM_GroupID = 29 FOR XML AUTO,ELEMENTS, BINARY BASE64)
print @.ss

So that i can use this paramer "@.ss" inside a insert statement.

Can any one help on this ?

Thanks.

You can’t do this in SQL Server 2000.

In SQL Server 2000 there is no XML data type, so you can’t able to store the FOR XML output into your local variable.

The first XML data type is introduced in SQL Server 2005. If you use SQL Server 2005 then your query will work fine. J

For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>

FOR XML AUTO change in 2005?

In 2005, the FOR XML AUTO seems to look into subselects and create nodes
based on those. This is different from 2000. Is this an enhancement or a
bug in '05?
We have a query that joins 4 select statements and returns them in XML.
e.g.
SELECT tree.tree
sr.Enum
sr.Name
cycle.Enum
cycle.Name
cycle.Code
task.TaskID
task.CycleID
task.Recipients
FROM
(select '' as tree) as tree
CROSS JOIN
(select dc.Enum
,r.Name
from dc inner join r on dc.dc_id = r.dc_id) as cycle
CROSS JOIN
(select dc.enum
.... <and so on>
The XML was returning the cycle values as dc and r elements instead of cycle
elements as it did in 2000.
I am just curious if this is getting fixed or if it is the expected behavior
going forward.
To fix this, we now use table variables in place of the embedded selects.
Thanks,
Steve Hughes
Yes, we have done some changes (bug fixes) on how the Auto mode treats
subselects. In SQL Server 2005 it will NOT look into the subselects, while
in 2000 it did. So for SQL Server 2005, derived tables can be used to group
under the same element columns from different tables, but hide the join from
AUTO mode heuristics.
There is an example in the compat level section in BooksOnLine at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm:
USE AdventureWorks
CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
SELECT * FROM (SELECT a.id AS a,
b.id AS b FROM Test a
JOIN Test b ON a.id=b.id)
Test FOR XML AUTO;
When the compatibility level for AdventureWorks is set to 80, the above
example produces:
<a a="1"><b b="1"/></a>
When the compatibility level for AdventureWorks is set to 90, the preceding
example produces:
<Test a="1" b="1"/>
<Test a="2" b="2"/>
For additional information about other FOR XML changes, please refer to
http://msdn.microsoft.com/library/de...forxml2k5.asp.
HTH
Michael
PS: If you see a different behaviour, please forward me a complete repro...
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
> In 2005, the FOR XML AUTO seems to look into subselects and create nodes
> based on those. This is different from 2000. Is this an enhancement or a
> bug in '05?
> We have a query that joins 4 select statements and returns them in XML.
> e.g.
> SELECT tree.tree
> sr.Enum
> sr.Name
> cycle.Enum
> cycle.Name
> cycle.Code
> task.TaskID
> task.CycleID
> task.Recipients
> FROM
> (select '' as tree) as tree
> CROSS JOIN
> (select dc.Enum
> ,r.Name
> from dc inner join r on dc.dc_id = r.dc_id) as cycle
> CROSS JOIN
> (select dc.enum
> ... <and so on>
> The XML was returning the cycle values as dc and r elements instead of
> cycle
> elements as it did in 2000.
> I am just curious if this is getting fixed or if it is the expected
> behavior
> going forward.
> To fix this, we now use table variables in place of the embedded selects.
> Thanks,
> Steve Hughes
>
>
>
|||This makes sense since we are running in 80 compatibility on SQL 2005 at the
moment. However, in 2000 it worked like it does in 90 compatability. I will
do some further testing to verify, but 80 is not truly running this the way
it did in SQL 2000 in either case.
I will try to follow up again shortly.
Thanks,
Steve
"Michael Rys [MSFT]" wrote:

> Yes, we have done some changes (bug fixes) on how the Auto mode treats
> subselects. In SQL Server 2005 it will NOT look into the subselects, while
> in 2000 it did. So for SQL Server 2005, derived tables can be used to group
> under the same element columns from different tables, but hide the join from
> AUTO mode heuristics.
> There is an example in the compat level section in BooksOnLine at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm:
> USE AdventureWorks
> CREATE TABLE Test(id int);
> INSERT INTO Test VALUES(1);
> INSERT INTO Test VALUES(2);
> SELECT * FROM (SELECT a.id AS a,
> b.id AS b FROM Test a
> JOIN Test b ON a.id=b.id)
> Test FOR XML AUTO;
> When the compatibility level for AdventureWorks is set to 80, the above
> example produces:
> <a a="1"><b b="1"/></a>
> When the compatibility level for AdventureWorks is set to 90, the preceding
> example produces:
> <Test a="1" b="1"/>
> <Test a="2" b="2"/>
> For additional information about other FOR XML changes, please refer to
> http://msdn.microsoft.com/library/de...forxml2k5.asp.
> HTH
> Michael
> PS: If you see a different behaviour, please forward me a complete repro...
> "Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
> news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
>
>
|||Ok. I would be interested in seeing the differences.
The problem with 2000 was that it was not necessarily predictable what
naming was chosen.
Best regards
Michael
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:1D2E34BB-BC26-4AA2-92C1-7EBDB5478709@.microsoft.com...[vbcol=seagreen]
> This makes sense since we are running in 80 compatibility on SQL 2005 at
> the
> moment. However, in 2000 it worked like it does in 90 compatability. I
> will
> do some further testing to verify, but 80 is not truly running this the
> way
> it did in SQL 2000 in either case.
> I will try to follow up again shortly.
> Thanks,
> Steve
> "Michael Rys [MSFT]" wrote:

FOR XML AUTO change in 2005?

In 2005, the FOR XML AUTO seems to look into subselects and create nodes
based on those. This is different from 2000. Is this an enhancement or a
bug in '05?
We have a query that joins 4 select statements and returns them in XML.
e.g.
SELECT tree.tree
sr.Enum
sr.Name
cycle.Enum
cycle.Name
cycle.Code
task.TaskID
task.CycleID
task.Recipients
FROM
(select '' as tree) as tree
CROSS JOIN
(select dc.Enum
,r.Name
from dc inner join r on dc.dc_id = r.dc_id) as cycle
CROSS JOIN
(select dc.enum
... <and so on>
The XML was returning the cycle values as dc and r elements instead of cycle
elements as it did in 2000.
I am just curious if this is getting fixed or if it is the expected behavior
going forward.
To fix this, we now use table variables in place of the embedded selects.
Thanks,
Steve HughesYes, we have done some changes (bug fixes) on how the Auto mode treats
subselects. In SQL Server 2005 it will NOT look into the subselects, while
in 2000 it did. So for SQL Server 2005, derived tables can be used to group
under the same element columns from different tables, but hide the join from
AUTO mode heuristics.
There is an example in the compat level section in BooksOnLine at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm:
USE AdventureWorks
CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
SELECT * FROM (SELECT a.id AS a,
b.id AS b FROM Test a
JOIN Test b ON a.id=b.id)
Test FOR XML AUTO;
When the compatibility level for AdventureWorks is set to 80, the above
example produces:
<a a="1"><b b="1"/></a>
When the compatibility level for AdventureWorks is set to 90, the preceding
example produces:
<Test a="1" b="1"/>
<Test a="2" b="2"/>
For additional information about other FOR XML changes, please refer to
http://msdn.microsoft.com/library/d... />
ml2k5.asp.
HTH
Michael
PS: If you see a different behaviour, please forward me a complete repro...
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
> In 2005, the FOR XML AUTO seems to look into subselects and create nodes
> based on those. This is different from 2000. Is this an enhancement or a
> bug in '05?
> We have a query that joins 4 select statements and returns them in XML.
> e.g.
> SELECT tree.tree
> sr.Enum
> sr.Name
> cycle.Enum
> cycle.Name
> cycle.Code
> task.TaskID
> task.CycleID
> task.Recipients
> FROM
> (select '' as tree) as tree
> CROSS JOIN
> (select dc.Enum
> ,r.Name
> from dc inner join r on dc.dc_id = r.dc_id) as cycle
> CROSS JOIN
> (select dc.enum
> ... <and so on>
> The XML was returning the cycle values as dc and r elements instead of
> cycle
> elements as it did in 2000.
> I am just curious if this is getting fixed or if it is the expected
> behavior
> going forward.
> To fix this, we now use table variables in place of the embedded selects.
> Thanks,
> Steve Hughes
>
>
>|||This makes sense since we are running in 80 compatibility on SQL 2005 at the
moment. However, in 2000 it worked like it does in 90 compatability. I wil
l
do some further testing to verify, but 80 is not truly running this the way
it did in SQL 2000 in either case.
I will try to follow up again shortly.
Thanks,
Steve
"Michael Rys [MSFT]" wrote:

> Yes, we have done some changes (bug fixes) on how the Auto mode treats
> subselects. In SQL Server 2005 it will NOT look into the subselects, while
> in 2000 it did. So for SQL Server 2005, derived tables can be used to grou
p
> under the same element columns from different tables, but hide the join fr
om
> AUTO mode heuristics.
> There is an example in the compat level section in BooksOnLine at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-860
2-48ebca266659.htm:
> USE AdventureWorks
> CREATE TABLE Test(id int);
> INSERT INTO Test VALUES(1);
> INSERT INTO Test VALUES(2);
> SELECT * FROM (SELECT a.id AS a,
> b.id AS b FROM Test a
> JOIN Test b ON a.id=b.id)
> Test FOR XML AUTO;
> When the compatibility level for AdventureWorks is set to 80, the above
> example produces:
> <a a="1"><b b="1"/></a>
> When the compatibility level for AdventureWorks is set to 90, the precedin
g
> example produces:
> <Test a="1" b="1"/>
> <Test a="2" b="2"/>
> For additional information about other FOR XML changes, please refer to
> http://msdn.microsoft.com/library/d...>
rxml2k5.asp.
> HTH
> Michael
> PS: If you see a different behaviour, please forward me a complete repro..
.
> "Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
> news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
>
>|||Ok. I would be interested in seeing the differences.
The problem with 2000 was that it was not necessarily predictable what
naming was chosen.
Best regards
Michael
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:1D2E34BB-BC26-4AA2-92C1-7EBDB5478709@.microsoft.com...
> This makes sense since we are running in 80 compatibility on SQL 2005 at
> the
> moment. However, in 2000 it worked like it does in 90 compatability. I
> will
> do some further testing to verify, but 80 is not truly running this the
> way
> it did in SQL 2000 in either case.
> I will try to follow up again shortly.
> Thanks,
> Steve
> "Michael Rys [MSFT]" wrote:
>

FOR XML AUTO broken in 2005

Re: http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx

I'm having exactly the same problem, although I'm writing queries that need to run on both SQL 2000 and 2005. I cannot believe this isn't a bug. Although I understand the logic behind the results I cannot accept the results in 2005 are correct. If each part a union produces a parent/child structure why is it considered correct that UNIONing the two produces a flat no-child relationship? It makes no sense, I don't want to see a compatible mode in a service pack for 2005 I want to see the bug fixed!

I understand your problem is that you want to write an XML publishing query with the specific UNION that works both in SQL Server 2000 and in SQL Server 2005. Please consider using FOR XML EXPLICIT - it should solve your problem.

Also note that SQL Server 2005 Service Pack 1 should contain a fix for the compatibility issue - FOR XML AUTO query with the UNION from the link you provided will work the same way between SQL Server 2000 and SQL Server 2005 SP1 for a database with 80 compatibility level, thus not breaking your application after upgrade to SQL Server 2005 SP1. FOR XML AUTO with the UNION on a database with 90 compatibility level will work the same in SQL Server 2005 RTM and SP1.

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reply, and yes I am using XML Explicit to work-around the problem...and it looks horrible.

I also understand that the service pack will have this special compatible mode, my point is that it shouldn't have. I don't understand the justification of letting 2005 produce results the way it does. I'm stating that this is a bug and should be fixed. I'd be very interested in learning why Microsoft feel this is a compatibility issue and not a bug. Given that if Select X...For XML AUTO produces a parent/child result surely unioning two lots of Select X would still produce a parent/child result?

|||

In addition to my explanations in http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx I'd say that while column naming derived from the first leg of UNION [ALL] is documented in BOL column-to-table association (which FOR XML AUTO uses) on top of UNION [ALL] was never documented; SQL Server 2000 behavior there is incorrect.

I generally discourage you from using AUTO mode of FOR XML on top of set operations (like UNION [ALL]/EXCEPT/INTERSECT) since it will prevent from using some performance optimizations we can do in AUTO mode. This is in SQL Server 2005. In SQL Server 2000 we would apply the optimizations but because of the buggy column-to-table associations we can get wrong results. I provided a repro for the wrong results below.

If you need UNION ALL and not UNION you may consider supplying two separate FOR XML AUTO and concatenating the results on the client side. This is given that you need to use a syntax that works on both SQL Server 200 and 2005. In SQL Server 2005 this can be achieved in a more explicit and cleaner way.

Here’s the SQL Server 2000 repro that produces wrong results. Notice different PK constraints on different tables and duplicate col1 values for t2 and t4.

create table t1(col1 int not null primary key, col2 varchar(256) not null)

insert t1 select 1,'t1col2row1'

insert t1 select 2,'t1col2row2'

go

create table t3(col1 int not null primary key, col2 varchar(256) not null)

insert t3 select 1,'t3col2row1'

insert t3 select 2,'t3col2row2'

go

create table t2(col1 int not null, col2 varchar(256) not null primary key)

insert t2 select 1,'t2col2row1'

insert t2 select 1,'t2col2row2'

go

create table t4(col1 int not null, col2 varchar(256) not null primary key)

insert t4 select 1,'t4col2row1'

insert t4 select 1,'t4col2row2'

go

select t1.col1,t1.col2 col12,t3.col2 from t3

inner join t1 on t3.col1 = t1.col1

union

select t2.col1,t2.col2 col12,t4.col2 from t4

inner join t2 on t4.col1 = t2.col1

for xml auto

go

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Again, thank you for the reply, interesting to hear that it doesn't perform very well. As I've mentioned I do understand the algorithm that is producing the results, what I'm saying is the algorithm is fundamentally flawed when used with UNIONs. To the user, SQL 2000 produces logical results whereas 2005 does not, for me that tells me that this is a bug. If you want to tell me that UNIONs and FOR XML AUTO are not longer supported but we'll provide a compat' mode, then I can swallow that. What I don't understand is the view that it's working fine in 2005 when clearly it doesn't.

As for the workarounds (and doesn't this also imply a bug) those are ok (not a great fan of using the client to do that) and I'm using the EXPLICIT XML alternative.

I don't want to appear argumentative but I would just like it to be recognised as a failing in 2005 and it should be documented as a breaking change rather than have a test fail or ,worse, have a customer report that upgrading to 2005 has broken their crucial application and have had to downgrade back to 2000! All of which could be avoided by clearly stating that there is a breaking change.

FOR XML AUTO - Performance under SQL 2000 SP3

I'm currently having a performance problem with a straight SQL select statement with the 'for xml auto' clause returning a result set involving approximately 5,000 records. This is taking about 16 seconds on a SQL 2000 installation SP3 (8.00.818), while it only takes a few milliseconds when removing the 'for xml auto' (when executed via Query Analyzer). Time under the 'for xml auto' is directly proportional to the number of records processed.

Testing the same query/table on another server with SP4 installed shows little difference in times.

I'll be trying to set up another test installation with SP3 on a different server to see if this behavior is something specific to SP3, but has anyone else encountered this problem?

Not anything specific to SP3...just tested it on a 8.00.818 box that I created and the query with/without the 'for xml auto' clause processed the results instantaneously. Will check to see what might be different about server configuration settings and collations.|||Seems to be associated with amount of data being output in the XML stream. The original output consists of 261 KB data and takes 16 seconds duration to execute. Renaming attributes in table to single characters to reduce tag name overhead reduces data size by 69% and duration to 70%. Execution was via Query Analyzer local to the database server. Execution on same table/data on another server was 115 ms. Query plans generated are the same. Output of data as a normal SQL result set (without 'for xml clause') on the problem installation takes time down to milliseconds also.|||

It is hard to give you any response to this without an exact repro (data and query at least). Did you compare the query plans?

The XML will be bigger than TDS rowset format but should not have such a big overhead. Are you sure you are not including the time it takes the QA to show the data?

Also, if the overhead does not show in SP4, could you just upgrade to SP4?

Best regards

Michael

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

FOR XML AUTO

Hi all, can anyone confirm for me whether or not MSDE provides the same FOR XML AUTO
Funtionality as the full version of SQL Server 2000?
I am thinking of using MSDE as our distributed DB, but cant find out in the documentation whether
it supports it or not.
thanks in advance,
Mike.
hi Mike,
"Mike UK" <Mike@.ISIS.co.uk> ha scritto nel messaggio
news:0C45E94F-AB45-4A94-8E04-35CC2A4B0563@.microsoft.com...
> Hi all, can anyone confirm for me whether or not MSDE provides the same
FOR XML AUTO
> Funtionality as the full version of SQL Server 2000?
> I am thinking of using MSDE as our distributed DB, but cant find out in
the documentation whether
> it supports it or not.
> thanks in advance,
> Mike.
I never heard of such a limitation, just tried a simple
select top 10 * from dbo.tbname for xml auto
... just works.. =;-)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea, thats great, I just saw an article that said MSDE didnt support
SQLXML functionality and so I was not sure.
Cheers!
Mike.

FOR XML and subselects

I thought SQL 2005 allowed this but perhaps i misread something.
SELECT column1, column2, ...
, (SELECT column1, column2, ... FROM table2 WHERE table2.column1 = table1.column1 FOR XML AUTO, TYPE) data
FROM Table1
That would return all the columns of table1 and a column of type xml for
the data in table2.
I have this:
select productid ,
(
select rtrim(xrefnum) xrefnum, qty from productxref where productid = '13737'
FOR XML path ('xref'),type
) xref
from product p
where productid = '13737'
which always fails with this:
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'XML'.
Is this possible?
danHello Dan,
Yes, it's possible.
Try this:
use northwind
go
select ProductID,
(select rtrim(productName) name
,supplierID
,categoryID
from dbo.products pi
where productName = 'Chai'
for xml path('pd'),type) xref
from dbo.products p
where productName = 'Chai'
go
If it works, I can't explain your problem unless your DBCOMPAT isn't 90.
If it doesn't work, post back what error message you get along with the output
of SELECT @.@.VERSION.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

FOR XML and performance

Does anyone have any suggestions or pointers for performance optimizations
for SELECT queries that take relational data and
return it as XML columns?
For example, I have a Person table. In our system, a person may have
multiple names, addresses, phone numbers, and email
addresses. So, I have a PersonNames table, PersonAddresses table,
PersonPhones table and a PersonEmails table. I'm
returning all of the person information at once, instead of having to make
multiple database calls.
One way to do this is to return multiple resultsets: the first is the person
base information, and each of the associated
collections of informataion would be in the next result sets.
There are some problems with this, though: if I ever want to bring back
multiple people with all of their supporting
information, I have to return them with multiple result sets in the same
order:
Person1
Person1Names
Person1Addresses
.
.
.
Person2
Person2Names
Person2Addresses
.
.
.
etc.
I've dealt with this by defining a view that selects the various information
as XML columns, like this:
SELECT personId,
dateOfBirth,
gender,
(SELECT DISTINCT nameId,
salutation,
firstName,
middleName,
surName,
suffix,
nameTypeId
FROM Names AS [Name]
WHERE personId = People.personId
FOR XML AUTO, TYPE, Root('Names')) AS Names,
(SELECT Address.addressId,
PeopleAddresses.addressTypeId AS addressTypeId,
cityId,
countyId,
stateId,
streetAddr1,
streetAddr2,
cityName,
stateAbbrev,
zipCode,
stateName,
countyName,
areaCode,
timeZone,
useDST
FROM PeopleAddresses INNER JOIN
VAddresses AS [Address] ON PeopleAddresses.addressId =
Address.addressId
WHERE PeopleAddresses.personId = People.personId AND
PeopleAddresses.addressId = Address.AddressId
FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
(SELECT emailAddressId,
emailAddress,
emailAddressTypeId
FROM EmailAddresses AS [EmailAddress]
WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
Root('EmailAddresses')) AS EmailAddresses,
(SELECT PhoneNumbers.phoneTypeId,
PhoneNumber.phoneNumberId,
PhoneNumber.areaCode,
PhoneNumber.phoneNumber,
PhoneNumber.extension,
PhoneNumber.prefix
FROM PeoplePhones PhoneNumbers INNER JOIN
PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
PhoneNumber.phoneNumberId
WHERE PhoneNumbers.personId = People.personId
FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
FROM dbo.People
This works really well - I can reach into the XML columns of the result set,
and pull back the data. I can also have a
SINGLE piece of code that knows how to read addresses, phone numbers, etc.
for other entities that have these. I just return
them in the queries for these entities as XML columns.
However, the performance is not optimal. I know that XML introduces a
performance penalty, and I'm willing to pay one for
this flexibility - but I was wondering if anyone has any performance
optimization tips for this scenario?
I could obviously change how I'm doing things: store the names, addresses,
etc. as XML, so that I only construct it once,
have special cases for returning this information as multiple result sets,
etc. I don't expect this to be the
best-performing method, but I'd like to eliminate as many performance
bottlenecks as possible.
Any comments are appreciated. Thanks!Hello PMarino,
If you need to read all that data then thats your option. However you are
readd all people in this example normally an app would only select 1 person.
Also you need to make sure the query is optimal so the subqueries are using
good plans
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Does anyone have any suggestions or pointers for performance
> optimizations for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to
> make multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring
> back multiple people with all of their supporting
> information, I have to return them with multiple result sets in the
> same order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
> I've dealt with this by defining a view that selects the various
> information as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO,
> TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS
> PhoneNumbers
> FROM dbo.People
> This works really well - I can reach into the XML columns of the
> result set, and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers,
> etc. for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names,
> addresses, etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result
> sets, etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>|||Did you try to analyze why your query is expensive? Can it be because of the
SELECT DISTINCT?
You can find some details of FOR XML implementation that could potentially
help optimize FOR XML query performance at
http://blogs.msdn.com/sqlprogrammab...ges/576095.aspx .
Best regards,
Eugene
"PMarino" <PMarino@.discussions.microsoft.com> wrote in message
news:B9127CC7-2413-4047-9E50-C779032E1586@.microsoft.com...
> Does anyone have any suggestions or pointers for performance optimizations
> for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to make
> multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person
> base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring back
> multiple people with all of their supporting
> information, I have to return them with multiple result sets in the same
> order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
>
> I've dealt with this by defining a view that selects the various
> information
> as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
> FROM dbo.People
>
> This works really well - I can reach into the XML columns of the result
> set,
> and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers, etc.
> for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names, addresses,
> etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result sets,
> etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>

FOR XML and performance

Does anyone have any suggestions or pointers for performance optimizations
for SELECT queries that take relational data and
return it as XML columns?
For example, I have a Person table. In our system, a person may have
multiple names, addresses, phone numbers, and email
addresses. So, I have a PersonNames table, PersonAddresses table,
PersonPhones table and a PersonEmails table. I'm
returning all of the person information at once, instead of having to make
multiple database calls.
One way to do this is to return multiple resultsets: the first is the person
base information, and each of the associated
collections of informataion would be in the next result sets.
There are some problems with this, though: if I ever want to bring back
multiple people with all of their supporting
information, I have to return them with multiple result sets in the same
order:
Person1
Person1Names
Person1Addresses
..
..
..
Person2
Person2Names
Person2Addresses
..
..
..
etc.
I've dealt with this by defining a view that selects the various information
as XML columns, like this:
SELECT personId,
dateOfBirth,
gender,
(SELECT DISTINCT nameId,
salutation,
firstName,
middleName,
surName,
suffix,
nameTypeId
FROM Names AS [Name]
WHERE personId = People.personId
FOR XML AUTO, TYPE, Root('Names')) AS Names,
(SELECT Address.addressId,
PeopleAddresses.addressTypeId AS addressTypeId,
cityId,
countyId,
stateId,
streetAddr1,
streetAddr2,
cityName,
stateAbbrev,
zipCode,
stateName,
countyName,
areaCode,
timeZone,
useDST
FROM PeopleAddresses INNER JOIN
VAddresses AS [Address] ON PeopleAddresses.addressId =
Address.addressId
WHERE PeopleAddresses.personId = People.personId AND
PeopleAddresses.addressId = Address.AddressId
FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
(SELECT emailAddressId,
emailAddress,
emailAddressTypeId
FROM EmailAddresses AS [EmailAddress]
WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
Root('EmailAddresses')) AS EmailAddresses,
(SELECT PhoneNumbers.phoneTypeId,
PhoneNumber.phoneNumberId,
PhoneNumber.areaCode,
PhoneNumber.phoneNumber,
PhoneNumber.extension,
PhoneNumber.prefix
FROM PeoplePhones PhoneNumbers INNER JOIN
PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
PhoneNumber.phoneNumberId
WHERE PhoneNumbers.personId = People.personId
FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
FROM dbo.People
This works really well - I can reach into the XML columns of the result set,
and pull back the data. I can also have a
SINGLE piece of code that knows how to read addresses, phone numbers, etc.
for other entities that have these. I just return
them in the queries for these entities as XML columns.
However, the performance is not optimal. I know that XML introduces a
performance penalty, and I'm willing to pay one for
this flexibility - but I was wondering if anyone has any performance
optimization tips for this scenario?
I could obviously change how I'm doing things: store the names, addresses,
etc. as XML, so that I only construct it once,
have special cases for returning this information as multiple result sets,
etc. I don't expect this to be the
best-performing method, but I'd like to eliminate as many performance
bottlenecks as possible.
Any comments are appreciated. Thanks!
Hello PMarino,
If you need to read all that data then thats your option. However you are
readd all people in this example normally an app would only select 1 person.
Also you need to make sure the query is optimal so the subqueries are using
good plans
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Does anyone have any suggestions or pointers for performance
> optimizations for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to
> make multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring
> back multiple people with all of their supporting
> information, I have to return them with multiple result sets in the
> same order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
> I've dealt with this by defining a view that selects the various
> information as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO,
> TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS
> PhoneNumbers
> FROM dbo.People
> This works really well - I can reach into the XML columns of the
> result set, and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers,
> etc. for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names,
> addresses, etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result
> sets, etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>
|||Did you try to analyze why your query is expensive? Can it be because of the
SELECT DISTINCT?
You can find some details of FOR XML implementation that could potentially
help optimize FOR XML query performance at
http://blogs.msdn.com/sqlprogrammability/pages/576095.aspx .
Best regards,
Eugene
"PMarino" <PMarino@.discussions.microsoft.com> wrote in message
news:B9127CC7-2413-4047-9E50-C779032E1586@.microsoft.com...
> Does anyone have any suggestions or pointers for performance optimizations
> for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to make
> multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person
> base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring back
> multiple people with all of their supporting
> information, I have to return them with multiple result sets in the same
> order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
>
> I've dealt with this by defining a view that selects the various
> information
> as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
> FROM dbo.People
>
> This works really well - I can reach into the XML columns of the result
> set,
> and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers, etc.
> for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names, addresses,
> etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result sets,
> etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>

FOR XML / stored procedures

Does anyone know how to generate a resultset using FOR XML
and save that result (XML document) into a column without
leaving SQL Server to render the document?I dont think this is possible as the FOR XML clause sends a stream data out
and it is not possible to save it into a varible at the SQL Server in the
current version atleast ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"T. Wade" <tnolte@.foundrysoftware.com> wrote in message
news:04ac01c366a0$fe916d90$a401280a@.phx.gbl...
> Does anyone know how to generate a resultset using FOR XML
> and save that result (XML document) into a column without
> leaving SQL Server to render the document?|||Hello Wade,
Thanks for posting to MSDN Managed Newsgroup. I will look into this issue
and
let you know as soon as I have update for you.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>|||Hello Wade,
There isn?t a way to do this without going out to a client and back in.
The FOR XML formatting of the recordset is done as the last step when the
TDS output stream is created so the output has to leave the server.
One workaround could be to use link servers by linking a server back to
itself. Ken Henderson's book contains related information
http://btobsearch.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=2VOBU
N18XR&btob=Y&isbn=0201700468&itm=1
[ Disclaimer: This is a third party info and Microsoft does not guarantee
the accuracy of it]
In above case, the data is still being streamed out of the server through
the OLEDB provider so you might wanna watch the performance.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
>Content-Class: urn:content-classes:message
>From: "T. Wade" <tnolte@.foundrysoftware.com>
>Sender: "T. Wade" <tnolte@.foundrysoftware.com>
>Subject: FOR XML / stored procedures
>Date: Tue, 19 Aug 2003 15:26:54 -0700
>Lines: 3
>Message-ID: <04ac01c366a0$fe916d90$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Thread-Index: AcNmoP6RD2nsbfwKTKGJ1OA7aiz6og==>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:302172
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Does anyone know how to generate a resultset using FOR XML
>and save that result (XML document) into a column without
>leaving SQL Server to render the document?
>

For XML -> ADO Recordset

I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
results of a SELECT...FOR XML statement. The SQL I use is:
http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
FOR XML AUTO,XMLDATA&root=Policies
This returns the Schema and Data, which I capture in strXML, and try to load
as follows:
Dim rs As New ADODB.Recordset
Dim s As New ADODB.Stream
...
s.Open
s.WriteText strXML
s.Position = 0
rs.Open s
I get the following Error on the last line:
"Recordset cannot be created from the Specified source. The source file or
stream must contain recordset data in XML if ADTG format."
I have also tried to save the XML to a file, and then do the following:
rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
This gives me the following error:
Recordset cannot be created. Source XML is incomplete or invalid.
The XML from the file loads into IE without error.
Is what I am trying to do possible without much trouble, or will I need to
use MSXML? Any FAQs or articles you could point me to would be appreciated.
TIA
Mike
Mike see my response to "A Mindboggingly simple question" Below
Basically this should do what you want
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
Obviously if you dont want to persist it you can simply stream the oDom.xml
Hope this helps
Graham
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
> results of a SELECT...FOR XML statement. The SQL I use is:
> http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
> FOR XML AUTO,XMLDATA&root=Policies
> This returns the Schema and Data, which I capture in strXML, and try to
load
> as follows:
> Dim rs As New ADODB.Recordset
> Dim s As New ADODB.Stream
> ...
> s.Open
> s.WriteText strXML
> s.Position = 0
> rs.Open s
> I get the following Error on the last line:
> "Recordset cannot be created from the Specified source. The source file
or
> stream must contain recordset data in XML if ADTG format."
> I have also tried to save the XML to a file, and then do the following:
> rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
> This gives me the following error:
> Recordset cannot be created. Source XML is incomplete or invalid.
> The XML from the file loads into IE without error.
> Is what I am trying to do possible without much trouble, or will I need to
> use MSXML? Any FAQs or articles you could point me to would be
appreciated.
> TIA
> --
> Mike
>
|||Graham:
I tried it, and am getting an error still. The Code is as follows:
Dim oCmd As Command
Dim oDom As IXMLDOMDocument2
Dim rs As New ADODB.Recordset
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
' Added next line to add a root node
oCmd.Properties("xml root") = "root"
oCmd.Execute , , 1024
oDom.save "f:\junk\results.xml"
' I get error "Recordset cannot be created. Source XML is incomplete or
invalid." on next line (err # -2147467259)
' although the xml loads into IE
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
Employees_sp source:
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees FOR XML AUTO, XMLDATA
I am using ADO 2.8
Any thoughts?
Thanks
Mike
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> Mike see my response to "A Mindboggingly simple question" Below
> Basically this should do what you want
> Sub SaveXml()
> Dim oCmd As Command
> Dim oPrm As Parameter
> Dim oDom As IXMLDOMDocument2
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
> Source=."
> oCmd.CommandText = "SQL_First"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> oCmd.Execute , , 1024
> oDom.Save "c:\temp\results.xml"
> End Sub
> Obviously if you dont want to persist it you can simply stream the
oDom.xml[vbcol=seagreen]
> Hope this helps
> Graham
> "Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
> news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
PolicyID='FPHM016182'[vbcol=seagreen]
> load
> or
to
> appreciated.
>
|||Mike,
The xml you are getting is not a persisted recordset it is simply pure xml
therefore you can't load it into a recordset. If all you want is a recordset
then just use a plain sp e.g.
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees
Dim oCmd As Command
Dim rs As New ADODB.Recordset
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
set rs=oCmd.Execute( )
then you can save the resulting recordset as xml with
rs.save "f:\junk\result.xml", 1
rs.close
and later do
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:uR922v%23cEHA.3632@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Graham:
> I tried it, and am getting an error still. The Code is as follows:
> Dim oCmd As Command
> Dim oDom As IXMLDOMDocument2
> Dim rs As New ADODB.Recordset
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
> "Security=SSPI;Persist Security Info=False;Initial
> Catalog=Northwind;Data " & _
> "Source=tcs2003s"
> oCmd.CommandText = "Employees_sp"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> ' Added next line to add a root node
> oCmd.Properties("xml root") = "root"
> oCmd.Execute , , 1024
> oDom.save "f:\junk\results.xml"
> ' I get error "Recordset cannot be created. Source XML is incomplete or
> invalid." on next line (err # -2147467259)
> ' although the xml loads into IE
> rs.Open "f:\junk\results.xml", "Provider=MSPersist"
> Employees_sp source:
> CREATE PROCEDURE Employees_sp
> AS
> SELECT * FROM Employees FOR XML AUTO, XMLDATA
> I am using ADO 2.8
> Any thoughts?
> Thanks
> --
> Mike
> "Graham Shaw" <Graham@.somewhere.com> wrote in message
> news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> oDom.xml
> PolicyID='FPHM016182'
to[vbcol=seagreen]
file[vbcol=seagreen]
following:[vbcol=seagreen]
need
> to
>

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
Thanks
Your provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks
|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For example,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>
|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/samp...medxmlauto.zip
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks

FOR XML - Root Element

Hi,
Does anybody know how to add a root element to the output of a stored
procedure that returns data, using FOR XML statement. I am using SQL server
2000.
ThanksYour provider should have the ability to set the root property. For example,
the ADO ICommandStream has a property that will add a root element. Ditto
for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks|||Michael,
Thanks for your response.
I can't find any property that will allow me to specify a root element. I
am using a XmlReader and sqlCommand to serialize an object based on the
results of a stored procedure which returns XML.
Due to our environment it is not possible to use a SqlXMLcommand object.
Thanks for any further help.
Alex
"Michael Rys [MSFT]" wrote:

> Your provider should have the ability to set the root property. For exampl
e,
> the ADO ICommandStream has a property that will add a root element. Ditto
> for the ADO.Net SQLXML extensions. Ditto for the SQLXML ISAPI URL queries.
> Best regards
> Michael
> "Redowl" <Redowl@.discussions.microsoft.com> wrote in message
> news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
>
>|||I think you have to use the SQLXML interfaces to add the root property.
In SQL Server 2000, you can also add a select N'<root>' before and select
N'</root>' after the stored proc invocation, IF you use the stream
interface.
Note that this is however not guaranteed to work well with the XML datatype
in 2005.
Also, in SQL Server 2005, you will be able to specify the root in your FOR
XML clause using a new ROOT directive...
Best regards
Michael
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:0DACE436-377C-489B-A378-166ADEB6F0D5@.microsoft.com...
> Michael,
> Thanks for your response.
> I can't find any property that will allow me to specify a root element. I
> am using a XmlReader and sqlCommand to serialize an object based on the
> results of a stored procedure which returns XML.
> Due to our environment it is not possible to use a SqlXMLcommand object.
> Thanks for any further help.
>
> Alex
> "Michael Rys [MSFT]" wrote:
>|||In case you are still contemplating the answer to this in SQL Server 2000,
here is a great white paper from Dan Sullivan on how to achieve this:
http://www.sqlservicebroker.com/sam...rmedxmlauto.zip
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Redowl" <Redowl@.discussions.microsoft.com> wrote in message
news:FD0FD005-2E9A-485D-85F7-FC7D66A23C89@.microsoft.com...
> Hi,
> Does anybody know how to add a root element to the output of a stored
> procedure that returns data, using FOR XML statement. I am using SQL
> server
> 2000.
> Thanks

FOR XML

Hello
I'm performing a query against a db with "for xml auto, elements" clause
Something like
Table
Field 0 Setas Primary Key
Field 1
Field 2
SELECT field1, field2 FROM table FOR XML AUTO,ELEMENTS
If I run the query into Query Analyzer I get the result I aspect
<table>
<field1>value</field1>
<field2>value</field2>
<table>
<table>
<field1>value</field1>
<field2>value</field2>
<table>
but if I run the same query from a .vbs script (using wscript to launch it)
I get the following
<table>
<field0>value</field0>
<field1>value</field1>
<field2>value</field2>
<table>
<table>
<field0>value</field0>
<field1>value</field1>
<field2>value</field2>
<table>
The primary key is automatically inserted in the xml
Does somebody know the reason? how to workaround it? or to control it?
ThanksPerhaps your process needs to more specifically map the elements:
http://www.eggheadcafe.com/articles/20030804.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:%23%23A2vhuKFHA.4092@.tk2msftngp13.phx.gbl...
> Hello
> I'm performing a query against a db with "for xml auto, elements" clause
> Something like
> Table
> Field 0 Setas Primary Key
> Field 1
> Field 2
> SELECT field1, field2 FROM table FOR XML AUTO,ELEMENTS
> If I run the query into Query Analyzer I get the result I aspect
> <table>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> <table>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> but if I run the same query from a .vbs script (using wscript to launch
> it) I get the following
> <table>
> <field0>value</field0>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> <table>
> <field0>value</field0>
> <field1>value</field1>
> <field2>value</field2>
> <table>
> The primary key is automatically inserted in the xml
> Does somebody know the reason? how to workaround it? or to control it?
> Thanks
>
>

FOR UPDATE Trigger on Subscriber Table Won't Fire

Jim,
it is possible that your publisher update is being
replicated as an delete/insert pair. You can run profiler
to check if this is the case. If so, there is a trace flag
to keep singleton updates replicated as updates. See this
article: http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q3
02/3/41.ASP&NoWebContent=1
HTH,
Paul Ibison
Thank you! It is indeed being sent as a delete/insert pair.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d51501c45383$f7401610$a001280a@.phx.gbl...
> Jim,
> it is possible that your publisher update is being
> replicated as an delete/insert pair. You can run profiler
> to check if this is the case. If so, there is a trace flag
> to keep singleton updates replicated as updates. See this
> article: http://support.microsoft.com/default.aspx?
> scid=http://support.microsoft.com:80/support/kb/articles/q3
> 02/3/41.ASP&NoWebContent=1
> HTH,
> Paul Ibison
>

For Update of Cursor in a UDF

Hi,
I am writing a UDF that returns a table variable. In the UDF, I have a
cursor that I want to update. I am getting a syntax error on the UPDATE.
Is there a reason I cannot do this is a user defined function?
Thanks
SteveYou cannot update data inside of a UDF. That restriction is in place,
AFAIK, to avoid some logic problems that might occur, e.g., if a scalar UDF
is being called row-by-row and updates rows that have already been
processed.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:3FE89A39-AB3F-4464-BBB3-ED652EB80D0A@.microsoft.com...
> Hi,
> I am writing a UDF that returns a table variable. In the UDF, I have a
> cursor that I want to update. I am getting a syntax error on the UPDATE.
> Is there a reason I cannot do this is a user defined function?
> Thanks
> Steve|||>> I am writing a UDF that returns a table variable. In the UDF, I have a
You cannot do any updates which changes the persisted data or the database
state from within a UDF. This is by design and documented in SQL Server
Books Online.
Perhaps if you post your overall requirements with relevant information,
others might suggest an alternative. Using a cursor inside a table-valued
UDF for updating certain data seems a very convoluted route.
Anith|||Why are you calling a UDF from a cursor? And are you sure you need to
use a cursor at all?
Please post DDL, sample data and explain your required end result if
you need more help.
David Portas
SQL Server MVP
--|||Thanks for all the responses, obviously, my approach was not too popular.
I like baseing ACCESS reports off of UDF table variables. For this
particular report, I need to process a lot of data and it required me to use
a cursor, and I wanted to update a column so the next pass through would kno
w
I had been there. If my UDF, I gather all this information and Insert it
into the table variable, then that is returned to ACCESS.
It is nice doing it with a UDF cause of the table variable. If I use a
Stored Proc, I would have to CREATE a temp table in the Proc and populate it
,
then base the report on the temp table, if it is still around.
Steve
"Anith Sen" wrote:

> You cannot do any updates which changes the persisted data or the database
> state from within a UDF. This is by design and documented in SQL Server
> Books Online.
> Perhaps if you post your overall requirements with relevant information,
> others might suggest an alternative. Using a cursor inside a table-valued
> UDF for updating certain data seems a very convoluted route.
> --
> Anith
>
>|||I think he's calling a cursor from a UDF :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124296660.434609.202840@.g14g2000cwa.googlegroups.com...
> Why are you calling a UDF from a cursor? And are you sure you need to
> use a cursor at all?
> Please post DDL, sample data and explain your required end result if
> you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||>> For this particular report, I need to process a lot of data and it
Cursors are seldom required for data updates. In most cases, you'd write a
single UPDATE statement, preferably within a stored procedure to do any
updates, but it depends on what you exactly meant by "process"
If you are interested in getting some additional assistance, please go
through www.aspfaq.com/5006 and post relevant information for others to
better understand your problem scenario.
Anith|||Thanks for the information. I don't update via a Cursor that often, but in
this case, it was sitting on the row I wanted update, and just thought it
would be convienient. Either way though, if I can't do any updates in a UDF
,
I am taking the wrong approach.
Thanks
"Anith Sen" wrote:

> Cursors are seldom required for data updates. In most cases, you'd write a
> single UPDATE statement, preferably within a stored procedure to do any
> updates, but it depends on what you exactly meant by "process"
> If you are interested in getting some additional assistance, please go
> through www.aspfaq.com/5006 and post relevant information for others to
> better understand your problem scenario.
> --
> Anith
>
>

FOR UPDATE clause in cursor

I'm refactoring a cursor written by another employee. It includes a cursor
that uses the FOR UPDATE clause to update records as the cursor iterates
through the records. Please assume that the cursor cannot/should not be
replaced.
When executing the snippet of code below, I get the SQL Server error below.
What syntax would allow me to make the cursor read/write rather than read
only? I tried making the cursor DYNAMIC, but this did not appear to help.
Thanks in adavnce.
-Mark
Server: Msg 16957, Level 16, State 4, Procedure p_om_daily_reload, Line 402
FOR UPDATE cannot be specified on a READ ONLY cursor.
********************** SNIPPET OF CODE ****************************
declare @.audit_note varchar(200)
declare reg_curs_mentorhip_cancelled CURSOR DYNAMIC
FOR
-- Look for existing payment records using the reg_num that have been
marked as canceled in informix.
SELECT DISTINCT mp.mentorship_id, im.hours
FROM t_om_informix_mentorship im
INNER JOIN t_om_app_mentorship_payment mp
ON im.reg_num = mp.reg_num
WHERE im.reg_status = 'C'
FOR UPDATE -- MARK: Added to allow cursor data to be updated. Default is
read-only
OPEN reg_curs_mentorhip_cancelled
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
PRINT'* 4a , subtract the amount of time refunded'
UPDATE t_om_app_mentorship
SET time_purchased = time_purchased - @.add_purchase
WHERE mentorship_id = @.mentorship_id;
PRINT'* 4a , remove payment record'
DELETE t_om_app_mentorship_payment
WHERE CURRENT OF reg_curs_mentorhip_cancelled
PRINT'* 4a ,add audit trail'
SET @.audit_note = 'mentorship_id = ' + convert(varchar(15),
@.mentorship_id) + ', Hours affected: ' + CAST(@.add_purchase as varchar)
EXEC p_om_data_audit_trail_i @.user_key = 'unknown', @.priority = 1,
@.create_user = 'Reload_Script', @.category = 'Payments',
@.issue = 'Mentorship Payment either disappeared or was cancelled',
@.note = @.audit_note
END
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
END
CLOSE reg_curs_mentorhip_cancelled
DEALLOCATE reg_curs_mentorhip_cancelledThe cursor cannot be updateable because of the DISTINCT modifier in the
query.

> Please assume that the cursor cannot/should not be
> replaced.
No chance of me assuming that! Cursors bad. Updateable cursors worse.
;-)
David Portas
SQL Server MVP
--|||>> Please assume that the cursor cannot/should not be replaced. <<
Is this a teaching exercise of some kind? "Look Igor, screwdriver
better than rocks for cabinet making!!" "Igor not like screwdriver!
Igor have rock!"
Based on ~20 years with SQL, ten years on the ANSI Standards committee
of SQL, and six books on the language, I find Kosher Pork easier to
assume -- Hey, we got genetic engineering and can fix those feet!
I have written five cursors in my career and I know that at least three
of them could have been done with a CASE expression. They would have
run **orders of magnitude** faster and been easierr to maintain.
Also, when you post code could you consistently uppercase reserved
words? Learn about semi-colons in SQL? ISO_11179 rules? Not use PRINT
in application code (it is for debugging only)? What about the basic
rule that audit trails are external to the procedures, so you know that
they valid?
You have code like an OO or COBOL program. The basic problem; this is
procedural code written in SQL, a declarative language. Your entire
approach and vocabulary are non-relational (Records in SQL').
Dave was nicer than me (who isn't?). EVERYTHING needs to be re-done
whiel there is still time. This is so crappy it is not worh saving and
might have screwed up data integrity already.

For Travis Lowdermilk

I dont know if anyone mentioned this, but I would like to have the ability to disable objects in a Data Flow Task

Travis,

I've split this into a seperate thread because it isn't relevant to the previous thread.

What is the scenario for wanting to do this? I don't understand why this would be required or how it would even be possible.

e.g. If you have a data-flow with 3 components, components 2 & 3 would not be able to execute if component 1 were disabled. The unit of execution in SSIS is a task, not a component.

-Jamie

|||

Jamie -

I apologize for posting in the wrong topic.

The reason I think it would be cool to have the ability to disable a particular object in a Data Flow Task is:

Scenario:

Let say I have a Multicast that splits the data to into two different directions:

Direction 1: goes to a JOIN and then eventually to an OLE DB DELETE Transform

Direction 2: goes through a Lookup transform and then to an OLE DB UPDATE or INSERT.

I may want to test just Direction 2 exclusively (maybe I know Direction 1 isn't going to work yet <maybe there was a change on the db side that makes this path invalid now>)

It would be nice to disable the path on the Direction 1 side and just focus on Direction 2.

Or maybe I want to just see if the LOOKUP (Direction 1 path) is redirecting the correct number of rows but I don't want to execute the INSERT OLE DB at the end of the path.

I have already created an extensive OLE DB INSERT statement that I don't want to have to recreate. Disabling it would be nice.

I am no expert at this sort of stuff, so it is quite possible that my sense of design is flawed and therefor I have to rely on deleting/cut and pasting items in the Data Flow Task to have them be ignored during debugging.

Hope that makes sense :-)

|||

Yeah that does kinda make sense. I guess that rather than disabling a component it'd be more accurate to say "stop the flow at this point".

I think its a valid request. You should request it at the feedback center.

In the meantime you can achieve the same thing by putting a data viewer on the path that you're not interested in. That'll effectively halt the flow.

-Jamie

|||

Thats a great idea.

Thanks!