Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

SQL For Xml - Master Detail Nesting


Hello,

I will step right into this with an example. Let's assume we have the
following tables:
CREATE TABLE #ProductType(
        ProductTypeId int Primary key,
        ProductTypeDescription varchar(50)
)
Insert Into #ProductType Values (1, 'Gizmos')
Insert Into #ProductType Values (2, 'Gadgets')

CREATE TABLE #Product(
        ProductId int Primary key,
        ProductTypeId int,
        ProductDescription varchar(50)
)

Insert Into #Product Values (1, 1, 'Gizmo Bar')
Insert Into #Product Values (2, 2, 'Gadget Camera')
Insert Into #Product Values (3, 1, 'Gizmobile')
Insert Into #Product Values (4, 2, 'foo Gadget')
Insert Into #Product Values (5, 1, 'Gizmonet')
Insert Into #Product Values (6, 2, 'Gadgetoscope')
Insert Into #Product Values (7, 2, 'Gadget x')
Insert Into #Product Values (8, 2, 'Gadget z')

Now that we have our tables lets try and return our data with xml

Select
        ProductTypeDescription "@ProductTypeDescription",
        ProductId "Product/@Product",
        ProductDescription "Product/@ProductDescription"
from
        #Product P
        INNER JOIN #ProductType PT on P.ProductTypeId = PT.ProductTypeId
FOR XML Path ('ProductType'), Root('Products'), Type

The above statement returns something like:

<Products>
  <ProductType ProductTypeDescription="Gizmos">
    <Product Product="1" ProductDescription="Gizmo Bar" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="2" ProductDescription="Gadget Camera" />
  </ProductType>
  <ProductType ProductTypeDescription="Gizmos">
    <Product Product="3" ProductDescription="Gizmobile" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="4" ProductDescription="foo Gadget" />
  </ProductType>
  <ProductType ProductTypeDescription="Gizmos">
    <Product Product="5" ProductDescription="Gizmonet" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="6" ProductDescription="Gadgetoscope" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="7" ProductDescription="Gadget x" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="8" ProductDescription="Gadget z" />
  </ProductType>
</Products>

But I would like something like:
<Products>
  <ProductType ProductTypeDescription="Gizmos">
    <Product Product="1" ProductDescription="Gizmo Bar" />
    <Product Product="3" ProductDescription="Gizmobile" />
    <Product Product="5" ProductDescription="Gizmonet" />
  </ProductType>
  <ProductType ProductTypeDescription="Gadgets">
    <Product Product="2" ProductDescription="Gadget Camera" />
    <Product Product="4" ProductDescription="foo Gadget" />
    <Product Product="6" ProductDescription="Gadgetoscope" />
    <Product Product="7" ProductDescription="Gadget x" />
    <Product Product="8" ProductDescription="Gadget z" />
  </ProductType>
</Products>

How would I go about doing this?

Also how would I go about doing this if I was querying against the following
view?

Create view ProductListing
AS
--You will need perm tables to run this statement
Select
        ProductType.ProductTypeId
        ProductTypeDescription ,
        ProductId ,
        ProductDescription      
from
        Product P
        INNER JOIN ProductType PT on P.ProductTypeId = PT.ProductTypeId

Thanks in advance,
Anastasios

Select
        ProductTypeDescription "@ProductTypeDescription",
        (Select ProductId "@Product",
                ProductDescription "@ProductDescription"
         from #Product P
         where P.ProductTypeId = PT.ProductTypeId
         order by ProductId
         FOR XML PATH('Product'),TYPE)
from #ProductType PT
FOR XML Path ('ProductType'), Root('Products'), Type

-----------------------------------------------Reply-----------------------------------------------
Thanks for your reply,

My problem is that I have the data as a flat structure i.e I have access  
only to a view with columns ProductTypeId, ProductTypeDescription, ProductId,
ProductName.  

Would you have any suggestions on how I can query the flat structure and
return the same resultset as your query below?

Not particularly efficient, but should work.

Select
        ProductTypeDescription "@ProductTypeDescription",
        (Select ProductId "@Product",
                ProductDescription "@ProductDescription"
         from ProductListing P
         where P.ProductTypeId = PT.ProductTypeId
         order by ProductId
         FOR XML PATH('Product'),TYPE)
from (select distinct ProductTypeId,ProductTypeDescription from
ProductListing) PT
FOR XML Path ('ProductType'), Root('Products'), Type

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc