How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

I've tried using "FOR XML PATH", "FOR XML EXPLICIT" and "FOR XML AUTO" but the data is never structured with the correct heirarchy.

Basically, I have one parent table (Customers) and 3 child tables. Each table has a customerid column. There is a one-to-many relationship from the Customers table to each of the 3 child tables.

As a mock example, I have a parent "Customers" table, and I have 3 other tables - Products, Hobbies and Vehicles - all related to the Customers table by a customerid.

What is the SQL code to achieve the following kind of structure -

Answer by Dibstar for How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

Using FOR XML RAW

IF OBJECT_ID ('tempdb..#customer') IS NOT NULL DROP TABLE #Customer  IF OBJECT_ID ('tempdb..#product') IS NOT NULL DROP TABLE #product  IF OBJECT_ID ('tempdb..#vehicle') IS NOT NULL DROP TABLE #Vehicle  IF OBJECT_ID ('tempdb..#hobbies') IS NOT NULL DROP TABLE #Hobbies    CREATE TABLE #Customer (id INT,name NVARCHAR(20))  INSERT INTO #customer SELECT 1,'Fred' UNION ALL SELECT 2,'Sue'    CREATE TABLE #product(customer_id INT, name NVARCHAR(20))  INSERT INTO #product  SELECt 1 AS id, 'table' as product   UNION ALL SELECT 1 AS id, 'chair' as product   UNION ALL SELECT 1 AS id, 'wardrobe' as product   UNION ALL SELECT 2 AS id, 'CD Player' as product   UNION ALL SELECT 2 AS id, 'Picture Frame' as product       CREATE TABLE #vehicle(customer_id INT, name NVARCHAR(20),colour NVARCHAR(20))  INSERT INTO #vehicle  SELECt 1 AS id, 'Car' as vehicle,'red' as colour  UNION ALL SELECT 1 AS id, 'bicycle' as vehicle,'Blue' AS colour  UNION ALL SELECT 2 AS id, 'Car' as vehicle, 'Yellow' as colour      CREATE TABLE #hobbies(customer_id INT, name NVARCHAR(20))  INSERT INTO #hobbies  SELECt 1 AS id, 'Golf' as name  UNION ALL SELECT 1 AS id, 'Swimming' as name  UNION ALL SELECT 2 AS id, 'Dancing' as name  UNION ALL SELECT 2 AS id, 'Reading' as name    SELECT   c.id AS id  ,c.name AS name  ,(SELECT p.name      FROM #product p      WHERE p.customer_id = c.id      FOR XML RAW('Products'),TYPE) AS Products  ,(SELECT h.name      FROM #hobbies h      WHERE h.customer_id = c.id      FOR XML RAW('Hobbies'),TYPE) AS Hobbies  ,(SELECT v.name,v.colour      FROM #vehicle v      WHERE v.customer_id = c.id      FOR XML RAW('Vehicle'),TYPE) AS Vehicle  FROM #customer c  FOR XML RAW('Customer'), ROOT('Customers')  

Answer by marc_s for How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

Try something like this - it uses FOR XML PATH and subselects to create the "linked" sub-nodes for a given customer (I limited this to two sub tables - but you should get the "gist" of it and be able to extend it to any number of linked subtables):

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

Gives me an output something like:

Answer by hiren patel for How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

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'

Answer by Roman Pekar for How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

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

Answer by user2583789 for How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent

DECLARE @sampleCount int =10  SET STATISTICS TIME ON       SELECT        (SELECT TOP  (@sampleCount) * FROM [Table1]TABLESAMPLE(100 PERCENT) FOR XML PATH('tbl1'), TYPE) AS 'tbl1',      (SELECT top (@sampleCount) * FROM [Table2] TABLESAMPLE(100 PERCENT)  FOR XML PATH('tbl2'), TYPE) AS 'tbl2',      (SELECT top (@sampleCount) * FROM [Table3] TABLESAMPLE(100 PERCENT) FOR XML PATH('tbl3'), TYPE) AS 'tbl3'      FOR XML PATH(''), ROOT('Table')  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72