imitating nested "FOREACH" loop in SQL Query

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

The APPLY operator will do what you need.

Check out:

http://msdn2.microsoft.com/en-us/library/ms175156.aspx

For a description and an example that pretty much is like your needs.

|||

In sql server 2005 you can use CTE..

Code Snippet

Create Table #folder (

[parentfolder] Varchar(100) ,

[childfolder] Varchar(100)

);

Insert Into #folder Values('X','Y');

Insert Into #folder Values('X','Z');

Insert Into #folder Values('Y','A');

Insert Into #folder Values('Y','B');

Insert Into #folder Values('B','C');

Insert Into #folder Values('B','D');

Insert Into #folder Values('C','E');

Insert Into #folder Values('C','F');

;With CTE([parentfolder],[childfolder],[Level],[Paths]) as

(

Select [parentfolder],[childfolder], 1 Level, Cast(Parentfolder + '\' + childfolder as varchar) Paths From #folder Where parentfolder = 'X'

UNION ALL

Select data.[parentfolder],data.[childfolder], Level + 1,Cast(Paths + '\' + data.[childfolder] as varchar)From #folder Data Join CTE On Data.ParentFolder = CTE.childfolder

)

Select * from CTE Order By Paths

|||Nicely done Mani!

I'm having issues with nested table...

Hey gang,

I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.

[ProductTable]<[CustomersTable]

[ProductTable] table only has product ID, and it is key.

[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.

I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).

Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.

Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.

When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.

Any ideas?

-Young K.

P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.

Just an idea... it might help to get all the columns needed in a single view in the database insted of using nested tables.

It's not the actual solution to your problem but it might be a workaround|||

I thought of that, and that lead me to my original question... How good are the estimation (or regression) type analysis if I build a single model for multiple products? For example, if I have data for customers buying cars, motorcycles and boats, should I put them all in one model ? Or should I build 3 different models for 3 seperate products?

Can I build a single model to predict who will buy a car and/or boats and/or motorcycle and/or boat? Should I build a seperate model for car, motorcycle and boat? Is there a difference in accuracy?

I assumed that I needed to build 3 models for 3 products. And I used nested table to create a single model for multiple products. With nested tables, I can clearly seperate data between different products.

Any thoughts?

-Young K.

|||

You can solve the problem you want to, but you are misusing nested tables.

The data you are analyzing is defined by your case table. The nested table simply describes attributes of your case. In your case you have "Products" as the case and "Customers" as an attribute of "Product". The key of the nested table indicates in individual attribute or set of attributes. For example, you will have an attribute "Customer 3's Gender". Of course, customer 3's gender is unlikely to change for each product in their basket.

Another way of thinking about the problem is that your case identifier indicates what is anonymous or "unimportant" about your model. You are trying to spot trends in product purchasing behavior across customers. An individual customer is anonymous or "unimportant", the information about the customer and the products they buy are important.

You could have customers as the case table and have a nested table of products that contains the product name and product revenue from that customer. You would need to make both the table and the product revenue "Predict Only" to ensure that the predictions are not influenced by other product revenues or the existence of other products. If you use Decision Trees in this case, you will get a tree for each product revenue based on customer demographics.

To get a prediction of a product revenure given customer info you would use a query something like this:

SELECT (SELECT Predict(Revenue) FROM Products WHERE [Product Name]='Car') as t FROM ...

|||

Thank you, Jamie. That helped a lot.

-Young K

Im having a bit of problem regarding XML output

Im having a bit of problem regarding XML output.
Im trying to make the info nested, like so:
- < product >
<soap>
<toothpaste>
and so on..
but instead Im getting it like this:
- < product >
<soap>
- < product >
<toothpaste>
I know i am problably forgetting some simple and stupid thing, but u know
how it is..
the code itself:
SELECT Rei.Hotel,
Rei.Payment,
Rei.InvoiceNr,
Rei.Total245,
Rei.Total14,
Rei.base245,
Rei.Base14,
Rei.Total_inc_tax,
prod.PrID,
prod.Price
FROM IceToStrengView AS Rei
LEFT OUTER JOIN
(
SELECT products.product AS PrID,
products.RNR,
products.customer,
products.Item AS Price
FROM Customer_brought AS products
) AS prod ON Rei.customerID = prod.customer AND Rei.RNR = prod.RNR
WHERE Rei.date BETWEEN (GETDATE()-1)
AND GETDATE()
Hlynur,
Have you considered using FOR XML EXPLICIT? Explicit mode gives you greater control over the XML that SQL is generating. In Explicit mode, you can use the UNION clause to create a "universal table". You should also try adding a root (parent) element to yo
ur XML.
Here is a Northwind example using FOR XML EXPLICIT with a root element. Also, a great reference for learning more about EXPLICIT mode can be found at http://www.topxml.com/sql/for_xml_explicit.asp.
SELECT
1 AS Tag,
NULL AS Parent,
Customers.CustomerID AS [Customer!1!CustomerID!hide],
Customers.CompanyName AS [Customer!1!CompanyName!element],
Customers.Address AS [Customer!1!Address!element],
Customers.City AS [Customer!1!City!element],
Customers.Region AS [Customer!1!Region!element],
Customers.PostalCode AS [Customer!1!PostalCode!element],
Customers.Country AS [Customer!1!Country!element],
Customers.ContactName AS [Customer!1!ContactName!element],
NULL AS [Order!2!OrderID],
NULL AS [Order!2!ShipVia],
NULL AS [OrderDetail!3!ProductID],
NULL AS [OrderDetail!3!ProductName],
NULL AS [OrderDetail!3!Quantity]
FROM
Customers
UNION ALL
-- 2. Second level of the hierarchy.
SELECT
2,
1,
Customers.CustomerID,
Customers.CompanyName,
Customers.Address,
Customers.City,
Customers.Region,
Customers.PostalCode,
Customers.Country,
Customers.ContactName,
Orders.OrderID,
Shippers.CompanyName,
NULL,
NULL,
NULL
FROM
Customers
JOIN
Orders
ON
Customers.CustomerID = Orders.CustomerID
JOIN
Shippers
ON
Orders.ShipVia = Shippers.ShipperID
UNION ALL
-- 3. Third level of the hierarchy.
SELECT
3,
2,
Orders.CustomerID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Orders.OrderID,
NULL,
[Order Details].ProductID,
Products.ProductName,
[Order Details].Quantity
FROM
Orders
JOIN
[Order Details]
ON
Orders.OrderID = [Order Details].OrderID
JOIN
Products
ON
[Order Details].ProductID = Products.ProductID
ORDER BY
[Customer!1!CustomerID!hide],
[Order!2!OrderID],
[OrderDetail!3!ProductID]
FOR XML EXPLICIT
-- Hlynur Tór Jónasson wrote: --
Im having a bit of problem regarding XML output.
Im trying to make the info nested, like so:
- < product ><soap><toothpaste>
and so on..
but instead Im getting it like this:
- < product ><soap>
- < product ><toothpaste>
I know i am problably forgetting some simple and stupid thing, but u know
how it is..
the code itself:
SELECT Rei.Hotel,
Rei.Payment,
Rei.InvoiceNr,
Rei.Total245,
Rei.Total14,
Rei.base245,
Rei.Base14,
Rei.Total_inc_tax,
prod.PrID,
prod.Price
FROM IceToStrengView AS Rei
LEFT OUTER JOIN
(
SELECT products.product AS PrID,
products.RNR,
products.customer,
products.Item AS Price
FROM Customer_brought AS products
) AS prod ON Rei.customerID = prod.customer AND Rei.RNR = prod.RNR
WHERE Rei.date BETWEEN (GETDATE()-1)
AND GETDATE()
|||Hi gang, thanks for the help, but now I have another problem, as you can
see all the "products ReiknID" are displayed together, they
do not come nested under the correct record... any help here :/ ?
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="Streaming not supported over multiple column result"?>
<Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324" />
<Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325" />
- <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
Total_inc_tax="0" Date="2004-06-09T13:39:31.967" bokunarNr="R991250\011"
ReiknNr="994199" Payment="1" InnriRID="7323">
<products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
<products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
<products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
<products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
<products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
<products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
<products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
<products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
<products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
<products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
<products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
<products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
<products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
</Rei>
</ROOT>
it should be like this
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="Streaming not supported over multiple column result"?>
<Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324" />
<products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
<products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
<products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
<products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
<products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
<products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
<Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325" />
<products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
<products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
<products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
<products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
<products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
<products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
<products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
- <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
Total_inc_tax="0" Date="2004-06-09T13:39:31.967" bokunarNr="R991250\011"
ReiknNr="994199" Payment="1" InnriRID="7323">
</Rei>
</ROOT>
this is the code
SELECT
1 as tag,
null as parent,
IceToStrengView.Hotel as [Rei!1!Hotel],
IceToStrengView.Total245 AS [Rei!1!Total245],
IceToStrengView.Total14 AS [Rei!1!Total14],
IceToStrengView.base245 AS [Rei!1!base245],
IceToStrengView.Base14 AS [Rei!1!Base14],
IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
IceToStrengView.Date AS [Rei!1!Date],
IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
IceToStrengView.Payment AS [Rei!1!Payment],
IceToStrengView.InnriRID AS [Rei!1!InnriRID],
null as [products!2!ReiknID],
null as [products!2!PrID],
null as [products!2!Price],
null as [products!2!Quantity]
FROM IceToStrengView
WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
Union All
SELECT
2,
1,
Hotel,
Total245,
Total14,
base245,
Base14,
Total_inc_tax,
Date,
bokunarNr,
ReiknNr,
Payment,
InnriRID,
tmpIceToStreng.ReiknID,
tmpIceToStreng.PrID,
tmpIceToStreng.Price,
tmpIceToStreng.Quantity
FROM IceToStrengView
INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
tmpIceToStreng.ReiknID
ORDER BY [Rei!1!Hotel],
[products!2!ReiknID]
FOR XML EXPLICIT
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:CAC21219-D90D-452D-AE0B-EAF4E84417E0@.microsoft.com...
> Hlynur,
> Have you considered using FOR XML EXPLICIT? Explicit mode gives you
greater control over the XML that SQL is generating. In Explicit mode, you
can use the UNION clause to create a "universal table". You should also try
adding a root (parent) element to your XML.
> Here is a Northwind example using FOR XML EXPLICIT with a root element.
Also, a great reference for learning more about EXPLICIT mode can be found
at http://www.topxml.com/sql/for_xml_explicit.asp.
> SELECT
> 1 AS Tag,
> NULL AS Parent,
> Customers.CustomerID AS [Customer!1!CustomerID!hide],
> Customers.CompanyName AS [Customer!1!CompanyName!element],
> Customers.Address AS [Customer!1!Address!element],
> Customers.City AS [Customer!1!City!element],
> Customers.Region AS [Customer!1!Region!element],
> Customers.PostalCode AS [Customer!1!PostalCode!element],
> Customers.Country AS [Customer!1!Country!element],
> Customers.ContactName AS [Customer!1!ContactName!element],
> NULL AS [Order!2!OrderID],
> NULL AS [Order!2!ShipVia],
> NULL AS [OrderDetail!3!ProductID],
> NULL AS [OrderDetail!3!ProductName],
> NULL AS [OrderDetail!3!Quantity]
> FROM
> Customers
> UNION ALL
> -- 2. Second level of the hierarchy.
> SELECT
> 2,
> 1,
> Customers.CustomerID,
> Customers.CompanyName,
> Customers.Address,
> Customers.City,
> Customers.Region,
> Customers.PostalCode,
> Customers.Country,
> Customers.ContactName,
> Orders.OrderID,
> Shippers.CompanyName,
> NULL,
> NULL,
> NULL
> FROM
> Customers
> JOIN
> Orders
> ON
> Customers.CustomerID = Orders.CustomerID
> JOIN
> Shippers
> ON
> Orders.ShipVia = Shippers.ShipperID
> UNION ALL
> -- 3. Third level of the hierarchy.
> SELECT
> 3,
> 2,
> Orders.CustomerID,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Orders.OrderID,
> NULL,
> [Order Details].ProductID,
> Products.ProductName,
> [Order Details].Quantity
> FROM
> Orders
> JOIN
> [Order Details]
> ON
> Orders.OrderID = [Order Details].OrderID
> JOIN
> Products
> ON
> [Order Details].ProductID = Products.ProductID
> ORDER BY
> [Customer!1!CustomerID!hide],
> [Order!2!OrderID],
> [OrderDetail!3!ProductID]
> FOR XML EXPLICIT
>
>
> -- Hlynur r Jnasson wrote: --
> Im having a bit of problem regarding XML output.
> Im trying to make the info nested, like so:
> - < product ><soap><toothpaste>
> and so on..
> but instead Im getting it like this:
> - < product ><soap>
> - < product ><toothpaste>
> I know i am problably forgetting some simple and stupid thing, but u
know
> how it is..
> the code itself:
> SELECT Rei.Hotel,
> Rei.Payment,
> Rei.InvoiceNr,
> Rei.Total245,
> Rei.Total14,
> Rei.base245,
> Rei.Base14,
> Rei.Total_inc_tax,
> prod.PrID,
> prod.Price
> FROM IceToStrengView AS Rei
> LEFT OUTER JOIN
> (
> SELECT products.product AS PrID,
> products.RNR,
> products.customer,
> products.Item AS Price
> FROM Customer_brought AS products
> ) AS prod ON Rei.customerID = prod.customer AND Rei.RNR =
prod.RNR
> WHERE Rei.date BETWEEN (GETDATE()-1)
> AND GETDATE()
>
>
|||This means that you have the wrong order by or forgot to add/repeat the
parent's identifying value that you order by and/or you order on the wrong
value.
In your case, you order on the wrong value, the [Rei!1!Hotel] value is not
unique, thus all the ones with value 1 is added before you get to the
products.
Try:
SELECT 1 as tag, null as parent,
IceToStrengView.Hotel as [Rei!1!Hotel],
IceToStrengView.Total245 AS [Rei!1!Total245],
IceToStrengView.Total14 AS [Rei!1!Total14],
IceToStrengView.base245 AS [Rei!1!base245],
IceToStrengView.Base14 AS [Rei!1!Base14],
IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
IceToStrengView.Date AS [Rei!1!Date],
IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
IceToStrengView.Payment AS [Rei!1!Payment],
IceToStrengView.InnriRID AS [Rei!1!InnriRID],
null as [products!2!ReiknID],
null as [products!2!PrID],
null as [products!2!Price],
null as [products!2!Quantity]
FROM IceToStrengView
WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
Union All
SELECT 2, 1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
InnriRID,
tmpIceToStreng.ReiknID,
tmpIceToStreng.PrID,
tmpIceToStreng.Price,
tmpIceToStreng.Quantity
FROM IceToStrengView
INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
tmpIceToStreng.ReiknID
ORDER BY [Rei!1!InnriRID], [products!2!ReiknID]
FOR XML EXPLICIT
HTH
Michael
"Hlynur r Jnasson" <hlybbi@.xodus.net> wrote in message
news:O8moDouTEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hi gang, thanks for the help, but now I have another problem, as you can
> see all the "products ReiknID" are displayed together, they
> do not come nested under the correct record... any help here :/ ?
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
> Server" Description="Streaming not supported over multiple column
> result"?>
> <Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
> Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
> bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324"
> />
> <Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
> Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
> bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325"
> />
> - <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
> Total_inc_tax="0" Date="2004-06-09T13:39:31.967"
> bokunarNr="R991250\011"
> ReiknNr="994199" Payment="1" InnriRID="7323">
> <products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
> <products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
> <products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
> <products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
> <products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
> <products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
> <products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
> <products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
> <products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
> <products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
> <products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
> <products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
> <products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
> </Rei>
> </ROOT>
> it should be like this
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
> Server" Description="Streaming not supported over multiple column
> result"?>
> <Rei Hotel="1" Total245="104695" Total14="0" base245="427292" Base14="0"
> Total_inc_tax="531987" Date="2004-06-10T12:07:24.123"
> bokunarNr="R990939\002" ReiknNr="994200" Payment="1" InnriRID="7324"
> />
> <products ReiknID="7324" PrID="2969" Price="2969" Quantity="1" />
> <products ReiknID="7324" PrID="2970" Price="2970" Quantity="1" />
> <products ReiknID="7324" PrID="2971" Price="2971" Quantity="1" />
> <products ReiknID="7324" PrID="2966" Price="2966" Quantity="1" />
> <products ReiknID="7324" PrID="2967" Price="2967" Quantity="1" />
> <products ReiknID="7324" PrID="2968" Price="2968" Quantity="1" />
> <Rei Hotel="1" Total245="15295" Total14="6729" base245="62423"
> Base14="48071" Total_inc_tax="132518" Date="2004-06-10T12:10:33.310"
> bokunarNr="R990939\005" ReiknNr="994201" Payment="1" InnriRID="7325"
> />
> <products ReiknID="7325" PrID="-99" Price="0" Quantity="0" />
> <products ReiknID="7325" PrID="2975" Price="2975" Quantity="1" />
> <products ReiknID="7325" PrID="2976" Price="2976" Quantity="1" />
> <products ReiknID="7325" PrID="2977" Price="2977" Quantity="1" />
> <products ReiknID="7325" PrID="2972" Price="2972" Quantity="1" />
> <products ReiknID="7325" PrID="2973" Price="2973" Quantity="1" />
> <products ReiknID="7325" PrID="2974" Price="2974" Quantity="1" />
> - <Rei Hotel="1" Total245="0" Total14="0" base245="0" Base14="0"
> Total_inc_tax="0" Date="2004-06-09T13:39:31.967"
> bokunarNr="R991250\011"
> ReiknNr="994199" Payment="1" InnriRID="7323">
> </Rei>
> </ROOT>
>
> this is the code
> SELECT
> 1 as tag,
> null as parent,
> IceToStrengView.Hotel as [Rei!1!Hotel],
> IceToStrengView.Total245 AS [Rei!1!Total245],
> IceToStrengView.Total14 AS [Rei!1!Total14],
> IceToStrengView.base245 AS [Rei!1!base245],
> IceToStrengView.Base14 AS [Rei!1!Base14],
> IceToStrengView.Total_inc_tax AS [Rei!1!Total_inc_tax],
> IceToStrengView.Date AS [Rei!1!Date],
> IceToStrengView.bokunarNr AS [Rei!1!bokunarNr],
> IceToStrengView.ReiknNr AS [Rei!1!ReiknNr],
> IceToStrengView.Payment AS [Rei!1!Payment],
> IceToStrengView.InnriRID AS [Rei!1!InnriRID],
> null as [products!2!ReiknID],
> null as [products!2!PrID],
> null as [products!2!Price],
> null as [products!2!Quantity]
> FROM IceToStrengView
> WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
> Union All
> SELECT
> 2,
> 1,
> Hotel,
> Total245,
> Total14,
> base245,
> Base14,
> Total_inc_tax,
> Date,
> bokunarNr,
> ReiknNr,
> Payment,
> InnriRID,
> tmpIceToStreng.ReiknID,
> tmpIceToStreng.PrID,
> tmpIceToStreng.Price,
> tmpIceToStreng.Quantity
> FROM IceToStrengView
> INNER JOIN tmpIceToStreng ON IceToStrengView.InnriRID =
> tmpIceToStreng.ReiknID
>
> ORDER BY [Rei!1!Hotel],
> [products!2!ReiknID]
>
> FOR XML EXPLICIT
>
>
> "mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
> news:CAC21219-D90D-452D-AE0B-EAF4E84417E0@.microsoft.com...
> greater control over the XML that SQL is generating. In Explicit mode, you
> can use the UNION clause to create a "universal table". You should also
> try
> adding a root (parent) element to your XML.
> Also, a great reference for learning more about EXPLICIT mode can be found
> at http://www.topxml.com/sql/for_xml_explicit.asp.
> know
> prod.RNR
>