connecting to SSAS: example

Showing posts with label example. Show all posts

Friday, March 9, 2012

Problem creating XML with FOR XML PATH -Resolved

Using Northwind as an example this is the XML I would like to create:

However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?

use northwind

select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
orderID 'Customer/Orders/Order/@.orderID',
convert(nvarchar(10),OrderDate,101) 'Customer/Orders/Order/@.orderdate'
from Customers
join Orders on Customers.CustomerID =Orders.CustomerID
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')

Partial Result of query:

<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
</Orders>
</Customer>
</CustomerOrders>
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10692" orderdate="10/03/1997" />
</Orders>
</Customer>
</CustomerOrders>...

Mickey:

My first pass at the problem looks like this:

Code Snippet

declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'

declare @.orders table
( customerId varchar(12),
orderId integer,
orderDate datetime
)
insert into @.orders
select 'ALFKI', 10643, '8/25/1997' union all
select 'ALFKI', 10692, '10/3/1997' union all
select 'ALFKI', 10702, '10/13/1997' union all
select 'ALFKI', 10835, '1/15/98' union all
select 'ALFKI', 10952, '3/17/98' union all
select 'ALFKI', 11011, '4/9/98'
--select * from @.orders

select replace(replace(
(
select customerId 'Customer/@.CustomerID',
companyName 'Customer/@.CompanyName',
( select orderId 'Order/@.OrderID',
convert(varchar, orderDate, 101) 'Order/@.orderdate'
from @.orders b
for xml path('')
) 'Customer/Orders'
from @.customers
for xml path(''), root('CustomerOrders')
), '<', '<'), '>', '>')
as theXml

/*
theXml

<CustomerOrders><Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"><Orders><Order OrderID="10643" orderdate="08/25/1997"/><Order OrderID="10692" orderdate="10/03/1997"/><Order OrderID="10702" orderdate="10/13/1997"/><Order OrderID="10835" orderdate="01/15/1998"/><Order OrderID="10952" orderdate="03/17/1998"/><Order OrderID="11011" orderdate="04/09/1998"/></Orders></Customer></CustomerOrders>
*/

/* Manually Reformatted:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order OrderID="10643" orderdate="08/25/1997"/>
<Order OrderID="10692" orderdate="10/03/1997"/>
<Order OrderID="10702" orderdate="10/13/1997"/>
<Order OrderID="10835" orderdate="01/15/1998"/>
<Order OrderID="10952" orderdate="03/17/1998"/>
<Order OrderID="11011" orderdate="04/09/1998"/>
</Orders>
</Customer>
</CustomerOrders>
*/

|||

After some research I was able to find a solution using a subselect.

Here is the code. Thanks to anyone who spent any time on this.

use northwind

select

Customers.CustomerID 'Customer/@.CustomerID',

CompanyName 'Customer/@.CompanyName',

(select

orderID '@.orderID',

convert(nvarchar(10),OrderDate,101) '@.orderdate'

from Orders

where Customers.CustomerID =Orders.CustomerID

FOR XML PATH ('Order'), Type ) 'Customer/Orders'

from Customers

where Customers.CustomerID='ALFKI'

FOR XML PATH ('CustomerOrders')

Problem creating XML with FOR XML PATH

Using Northwind as an example this is the XML I would like to create:

However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?

use northwind

Partial Result of query:

Mickey:

My first pass at the problem looks like this:

Code Snippet

declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'

/*
theXml

|||

After some research I was able to find a solution using a subselect.

Here is the code. Thanks to anyone who spent any time on this.

use northwind

select

Customers.CustomerID 'Customer/@.CustomerID',

CompanyName 'Customer/@.CompanyName',

(select

orderID '@.orderID',

convert(nvarchar(10),OrderDate,101) '@.orderdate'

from Orders

where Customers.CustomerID =Orders.CustomerID

FOR XML PATH ('Order'), Type ) 'Customer/Orders'

from Customers

where Customers.CustomerID='ALFKI'

FOR XML PATH ('CustomerOrders')

Saturday, February 25, 2012

Problem counting records

Hi,

I am struggling with a simple query, but I just don't see it.
I have the following example table.

Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0

The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.

What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.

ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0

Any assistance would be greatly appreciated.Did you think of this:

select t1.ID, t1.Subject, count(1) as Num_replies_to
from tbl t1
left join tbl t2
on t2.Reply_to=t1.ID
where t1.Reply_to=0
group by t1.ID, t1.Subject

Bye, Manfred|||What I would like to have returned is this: a list of messages where

Quote:

Originally Posted by

Reply_to = 0 and the number of replies to this message.

A subquery like the example below is one method.

SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@.netscape.netwrote in message
news:474fe374$0$22307$ba620e4c@.news.skynet.be...

Quote:

Originally Posted by

Hi,
>
I am struggling with a simple query, but I just don't see it.
I have the following example table.
>
Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0
>
The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.
>
What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.
>
ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0
>
Any assistance would be greatly appreciated.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I would like to have returned is this: a list of messages where
>Reply_to = 0 and the number of replies to this message.

>
A subquery like the example below is one method.
>
SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0
>

I knew it was simple. It had to be simple. I just didn't see it.
Many thanks to both Dan and Manfred.

Cheers.

Friday, March 9, 2012

Problem creating XML with FOR XML PATH -Resolved

Problem creating XML with FOR XML PATH

Saturday, February 25, 2012

Problem counting records

connecting to SSAS

Blog Archive

About Me