Sql Ignoring spaces: 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()
|||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
>

Monday, March 12, 2012

Im having a bit of problem regarding XML output

No comments:

Post a Comment

Sql Ignoring spaces

Blog Archive

About Me