Im having a bit of problem regarding XML output

[hlybbi]

Im having a bit of problem regarding XML output

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&#92;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&#92;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&#92;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="- <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&#92;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&#92;005" ReiknNr="994201" Payment="1" InnriRID="7325">
<products ReiknID="7324" PrID="15" Price="2969" Quantity="1" />
<products ReiknID="7324" PrID="16" Price="2970" Quantity="1" />
<products ReiknID="7324" PrID="17" Price="2971" Quantity="1" />
<products ReiknID="7324" PrID="15" Price="2966" Quantity="1" />
<products ReiknID="7324" PrID="16" Price="2967" Quantity="1" />
<products ReiknID="7324" PrID="17" Price="2968" Quantity="1" />
<products ReiknID="7325" PrID="-99" Price="13700" Quantity="4" />
<products ReiknID="7325" PrID="15" Price="2975" Quantity="1" />
<products ReiknID="7325" PrID="16" Price="2976" Quantity="1" />
<products ReiknID="7325" PrID="17" Price="2977" Quantity="1" />
<products ReiknID="7325" PrID="15" Price="2972" Quantity="1" />
<products ReiknID="7325" PrID="16" Price="2973" Quantity="1" />
<products ReiknID="7325" PrID="17" 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&#92;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&#92;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&#92;011"
ReiknNr="994199" Payment="1" InnriRID="7323">
</Rei>
</ROOT>


here is the code
CREATE PROCEDURE dbo.IceToStreng
AS

IF EXISTS(SELECT * FROM sysObjects WHERE name = 'tmpIceToStreng ' AND xtype='U')
DROP TABLE tmpIceToStreng

CREATE TABLE tmpIceToStreng
(
PrID int NULL,
Price int NULL,
Quantity int NULL,
customer int,
ReiknID int
)

insert tmpIceToStreng
select Customer_brought.product AS PrID,
Customer_brought.ID AS Price,
(1) AS Quantity,
Customer_brought.customer AS customer,
IceToStrengView.InnriRID AS ReiknID
FROM IceToStrengView
INNER JOIN Customer_brought ON IceToStrengView.customerID = Customer_brought.customer
WHERE IceToStrengView.date BETWEEN (GETDATE()-1)
AND GETDATE()

Select * FROM tmpIceToStreng

insert tmpIceToStreng
SELECT
- 99 AS PrID,
dbo.FuRoomPrice(herb_Action.dags_koma,herb_Action.gerd) AS Price,
dbo.SumTwoDateValues(herb_Action.dags_koma, herb_Action.dags_fara) AS Quantity,
herb_Action.users AS customer,
IceToStrengView.InnriRID AS ReiknID
FROM herb_Action INNER JOIN
IceToStrengView ON herb_Action.id = IceToStrengView.Herb_actionID
WHERE (IceToStrengView.Date BETWEEN GETDATE() - 1 AND GETDATE())
AND IceToStrengView.RNR = herb_Action.RNR


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


GO

Best regards Hlynur

http://www.xodus.net

 

[jms8222]

You probably want to check the order by in your SQL statement that is returning your XML

ORDER BY [Rei!1!Hotel], [products!2!ReiknID]

If you run your union query without using 'FOR XML EXPLICIT', do the rows come back in the order you want?? Once you've got them ordered correctly, the XML output should be correct as well.

 

[hlybbi]

if i dont use 'FOR XML EXPLICIT' the query doesnt work,
i thing it is ordered correccly

Best regards Hlynur

http://www.xodus.net