Comment renvoyer du XML à partir de SQL Server 2008 qui est structuré avec plusieurs sélections de partage d'un parent commun

Essayer quelque chose comme cela - il utilise POUR XML PATH et les sous-sélections pour créer le "lié" sous-nœuds, pour un client donné (- je limité à deux sous-tableaux - mais vous devriez obtenir le "résumé" de pouvoir l'étendre à un nombre quelconque de sous-tables):

SELECT
    CustomerID AS '@CustomerID',
    CustName AS '@Name',

    (SELECT ProductName AS '@productname'
     FROM dbo.Products p
     WHERE p.CustomerID = c.CustomerID  
     FOR XML PATH('Product'), TYPE) AS 'Products',

    (SELECT HobbyName AS '@hobbyname'
     FROM dbo.Hobbies h 
     WHERE h.CUstomerID = c.CustomerID
     FOR XML PATH('Hobby'), TYPE) AS 'Hobbies'
FROM
    dbo.Customers c
FOR XML PATH('Customer'), ROOT('Customers')

Me donne une sortie quelque chose comme:

<Customers>
  <Customer CustomerID="1" Name="Fred">
    <Products>
      <Product productname="Table" />
      <Product productname="Wardrobe" />
      <Product productname="Chair" />
    </Products>
    <Hobbies>
      <Hobby hobbyname="Golf" />
      <Hobby hobbyname="Swimming" />
    </Hobbies>
  </Customer>
  <Customer CustomerID="2" Name="Sue">
    <Products>
      <Product productname="CD Player" />
      <Product productname="Picture frame" />
    </Products>
    <Hobbies>
      <Hobby hobbyname="Dancing" />
      <Hobby hobbyname="Gardening" />
      <Hobby hobbyname="Reading" />
    </Hobbies>
  </Customer>
</Customers>

select
    c.customerid,
    c.name,
    (
        select p.productname
        from Products as p
        where p.customerid = c.customerid
        for xml raw('Product'), root('Products'), type
    ),
    (
        select h.hobbyname
        from Hobbies as h
        where h.customerid = c.customerid
        for xml raw('Hobby'), root('Hobbies'), type
    ),
    (
        select v.name, v.color
        from Vehicles as v
        where v.customerid = c.customerid
        for xml raw('Vehicle'), root('Vehicles'), type
    )
from Customers as c
for xml raw('Customer'), root('Customers')

=> sql violon démo

USE [EAPP_BranchDb]
GO
/****** Object:  StoredProcedure [dbo].[SP_procXMLOutput]    Script Date: 09/05/2013 15:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_procXMLOutput] 
@Data XML  OUTPUT 
AS
BEGIN
DECLARE @myDoc xml,
@myDoc1 xml,
@var_Doc1 nvarchar(max),
@myDoc2 xml,
@var1 nvarchar(max),
@var2 nvarchar(max),
@var3 nvarchar(max),
@var_id as int,
@var_id1 as nvarchar(10),
@var_grp_id1 as nvarchar(10),
@var_parent_id as bigint,
@var_grp_id as nvarchar(10),
@var_parent_type as nvarchar(1),
@var_type as nvarchar(10),
@xml XML,
@grp1 nvarchar(max),
@grp2 nvarchar(max)
DECLARE xml_cur_id CURSOR FOR
SELECT id, parent_id, grp_id, parent_type,type FROM xml_table where parent_type='P' order by id
DECLARE xml_cur_grpid CURSOR FOR
SELECT GRP_ID FROM xml_table WHERE parent_id=6 and parent_type='C' group by GRP_ID
BEGIN
SET @myDoc = N'<frameSet label="Personal Details" id="1"         
</frameSet';
SET @var_Doc1='<field pid="6" type="ROW" /</field';               
OPEN xml_cur_id
FETCH NEXT FROM xml_cur_id INTO @var_id, @var_parent_id, @var_grp_id, @var_parent_type,@var_type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @var_type<'TABLE' and @var_parent_type='P'  
BEGIN   
SET @myDoc1=(SELECT id, name, type, isMandtory, isDependent, isDependentValue,dataType, lookupPath, lookupName, defaultValue, minDate, maxDate, fieldLabel, maxLength, readonly, disabled, onChangeEvent, reloadEvent,  validationMessage, tabindex FROM xml_table WHERE id=@var_id FOR XML RAW('field')) 
SET @myDoc.modify('insert sql:variable("@myDoc1") as last into (/frameSet)[1] ')
END 
IF @var_type='TABLE' and @var_parent_type='P'   
BEGIN
/*table parent creation START*/ 
SET @myDoc1=(SELECT id, name, type,fieldLabel FROM xml_table WHERE id=@var_id FOR XML RAW('field')) 
SET @myDoc.modify('insert sql:variable("@myDoc1") as last into (/frameSet)[1] ')
/*table parent creation END*/   
/*table COLUMN creation START*/
SELECT @var1=(SELECT type,fieldLabel FROM XML_RELATION_TABLE WHERE Parent_id=6 order by id FOR XML RAW('field'))
SELECT @var2=(SELECT '<field type="ROW"'+@var1+'</field')
SELECT @myDoc1=@var2
SET @var_id1=@var_id
/*table COLUMN creation START*/
/*TABLE CHILD row creation START*/
SET @var3=''
OPEN xml_cur_grpid
FETCH NEXT FROM xml_cur_grpid INTO @var_grp_id1
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @grp1=(SELECT id, name, type, isMandtory, isDependent, isDependentValue,dataType, lookupPath, 
lookupName, defaultValue, minDate, maxDate, fieldLabel, maxLength, 
readonly, disabled, onChangeEvent, reloadEvent,  validationMessage, tabindex FROM xml_table 
WHERE parent_id=@var_parent_id and parent_type='C' and grp_id=@var_grp_id1 order by id
FOR XML RAW('field'))
SELECT @grp2=(SELECT '<field type="ROW"'+@grp1+'</field')
SET @var3=@var3+@grp2
FETCH NEXT FROM xml_cur_grpid INTO @var_grp_id1
END
CLOSE xml_cur_grpid
DEALLOCATE xml_cur_grpid            
/*TABLE CHILD row creation END*/
SELECT @myDoc1=@var2+@var3
SET @myDoc.modify('insert sql:variable("@myDoc1")  into (/frameSet/field[@id=sql:variable("@var_id1")])[1] ')
SET @var2=''
SET @var3=''
END
FETCH NEXT FROM xml_cur_id INTO @var_id, @var_parent_id, @var_grp_id, @var_parent_type,@var_type
END
CLOSE xml_cur_id
DEALLOCATE xml_cur_id
SET @Data=@myDoc
END        
END    
GO