Parameter used in "For XML Explicit" 
Author Message
 Parameter used in "For XML Explicit"

Hi,all
Can parameters is supported in SQL statement with "for XML
explict"
I make this statement, it suceeds.
---------------------------------
select 1 as tag, null as parent, customers.customerID as
[customer!1!customerID],
       null as [orders!2!orderID],null as [orderdetails!3!
productID]
from customers
union all
select 2, 1,customers.customerid,orders.orderid,null from
customers, orders where
      customers.customerid=orders.customerid
Union all
select 3,2,customers.customerID, orders.orderid,[order
details]. productid
from customers, orders,[order details] where
      customers.customerid=orders.customerid AND
orders.orderid=[order details].orderid
order by [customer!1!customerid], [orders!2!orderid],
[orderdetails!3!productID]
for XML explicit
go
-------------------
but it fails when I change the code like next:
-------------------------







from customers
union all

from customers, orders where
      customers.customerid=orders.customerid
Union all

details]. productid
from customers, orders,[order details] where
      customers.customerid=orders.customerid AND
orders.orderid=[order details].orderid


for XML explicit
go
-----------------
the error message is "there are invailid column

using TAGNAME!TAGID!ATTRIBUTENAME[!..] format"
Does subclause "For XML explicit" not support vars in it?
thanks very much



Thu, 03 Nov 2005 21:30:11 GMT
 Parameter used in "For XML Explicit"


[snip]

To do this you would need to use the exec statement. Example:


customer.customerID as [customer!' +

See the Sql books online for more information about exec.

--
Bryant



Sat, 05 Nov 2005 06:42:01 GMT
 Parameter used in "For XML Explicit"
Thanks Bryant, this is a good idea, but I'am afraid this would lead to
low performance because the exec statement can not be compiled unitl it
runs, while this is designated as an OLTP application, so it there
another solution with a high performance? Or maybe I have a mistake
about exec string method, it will not belowen the performance or only a
little?
thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 05 Nov 2005 08:40:03 GMT
 Parameter used in "For XML Explicit"

Quote:
> Thanks Bryant, this is a good idea, but I'am afraid this would lead to
> low performance because the exec statement can not be compiled unitl it
> runs, while this is designated as an OLTP application, so it there
> another solution with a high performance? Or maybe I have a mistake
> about exec string method, it will not belowen the performance or only a
> little?

If you want an opinion on the performance of the exec statement, just post
your question in the .programming group and I'm sure you will get some good
opinions on it. The problem is that the exec statement is the only way to do
what you want to do. So you could come up with a more complex solution (lots
of queries for each possible situation) or you can use the exec statement
and use your one-size-fits-all query.

--
Bryant



Sun, 06 Nov 2005 00:45:45 GMT
 Parameter used in "For XML Explicit"
Please note that you want to generate data-driven column names. This is not
allowed in SQL. So indeed the only way to do this is dynamically building
the query.

And yes, the performance is worse since it needs to compile the query at
every invocation. The actual overhead of course depends on the complexity of
the query and the optimizations that are being done.

Best regards
Michael
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.


Quote:


> > Thanks Bryant, this is a good idea, but I'am afraid this would lead to
> > low performance because the exec statement can not be compiled unitl it
> > runs, while this is designated as an OLTP application, so it there
> > another solution with a high performance? Or maybe I have a mistake
> > about exec string method, it will not belowen the performance or only a
> > little?

> If you want an opinion on the performance of the exec statement, just post
> your question in the .programming group and I'm sure you will get some
good
> opinions on it. The problem is that the exec statement is the only way to
do
> what you want to do. So you could come up with a more complex solution
(lots
> of queries for each possible situation) or you can use the exec statement
> and use your one-size-fits-all query.

> --
> Bryant



Tue, 08 Nov 2005 01:39:54 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Joins and "for xml explicit"

2. ""URGENT "" Inserting "For xml explicit" results to a Temp table

3. "Paging" XML Strings using SQL 2000

4. using the "for xml" clause

5. "C#" "FOR XML EXPLICIT" ERRORS

6. SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

7. max of ("...","...","..")

8. VarChar Parameter used for "IN" Clause

9. Facing problem in creating XML using FOR XML EXPLICIT

10. problem in creating XML using For XML explicit

11. Generating Schema compliant XML from SQL Server Database using SQLXML and XML Explicit -- [Performance/Maintenance issues]

12. Q: Hiding/not showing a column in the resulting xml using xml explicit


 
Powered by phpBB® Forum Software