query - need to include a column with xml output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query - need to include a column with xml output

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-09-01 : 14:13:59

Hello -

I am trying to write a query that will include both char/int colums as well as a column that needs to include some XML output.

For example, let's say I have a customer table, a product table and a table that has all the products purchased by customers as:


Customer
 (  CustomerID int IDENTITY (1,1),
    CustomerName nvarchar(50)
 )
Product
 ( ProductID int IDENTITY (1,1),
   ProductName nvarchar(50)
 )
CustomerProduct
 ( CustomerID,
   ProductID
 )

And, what I am trying to get from my query is something like:


     CustomerName   Products
     Joe Smith              (need an xml list here of product ids)
     Jane Doe

I can get an xml list of product ids...but I cannot get it by customer name in the same output.

thanks for any help!
. - will

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-01 : 15:10:49

Can you show us what your "xml list of product ids" for one customer should look like?

Be One with the Optimizer
TG

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-09-01 : 19:00:45

Sure...

Something like:


<row>
  <ProductID>1</ProductID>
</row>
<row>
  <ProductID>2</ProductID>
</row>
<row>
  <ProductID>3</ProductID>
</row>
<row>
  <ProductID>4</ProductID>
</row>

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-01 : 19:45:15

Here's one way:


use tempdb
go
create table Customer
 (  CustomerID int IDENTITY (1,1),
    CustomerName nvarchar(50)
 )
go
create table Product
 ( ProductID int IDENTITY (1,1),
   ProductName nvarchar(50)
 )
go
create table CustomerProduct
 ( CustomerID int,
   ProductID int
 )

go
set nocount on

insert customer (customerName)
select 'John Smith' union all
select 'Jane Doe'

insert product (productName)
select 'prod 1' union all
select 'prod 2' union all
select 'prod 3'

insert CustomerProduct
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 2,2 union all
select 2,3

select c.customerName, oa.ProductXML
from   Customer c
cross apply (
       select productid
       from   CustomerProduct
       where  customerid = c.customerid
       for xml path('row')
       )  oa (productXML)

go
drop table customerProduct
drop table product
drop table customer

output:
customerName                                       productXML
-------------------------------------------------- -----------------------------------------------------------------------------------------------------------
John Smith                                         <row><productid>1</productid></row><row><productid>2</productid></row>
Jane Doe                                           <row><productid>1</productid></row><row><productid>2</productid></row><row><productid>3</productid></row>

Be One with the Optimizer
TG

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-09-02 : 12:37:56

Thanks so much. That worked great. I haven't used Cross Apply enough...and I can see now where it helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources