Gewusst wie: zurückgeben von XML in SQL Server 2008 ist so strukturiert, mit mehreren Auswahlmöglichkeiten, die eine gemeinsame übergeordnete

Probieren Sie etwas wie dieses - es verwendet FÜR XML PATH und subselects zu erstellen, die "verlinkten" sub-Knoten für einen bestimmten Kunden (ich begrenzte diese auf zwei sub-Tabellen - aber Sie sollten das "wesentliche" der it und in der Lage sein zu verlängern, um eine beliebige Anzahl von verknüpften Untertabellen):

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')

Gibt mir eine Ausgabe etwa wie:

<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 fiddle-demo

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