How to combine two FOR XML AUTO into 1 XML?

Question

We are using SQL Server 2012.

Table myTbl has a one to many relationship to table myAllocation
Table ABC_myTbl has a one to many relationship to table ABC_myAllocation

The below query combined 2 FOR XML AUTO into 1 XML, but the problem is ID, SystemSource, Manager are included in element TradeTicket instead of on their own, and accountManager, unitPrice are included in element allocationRow instead of on their own.

Thank you

        SELECT ''+
    (SELECT 
            (   SELECT trTicket.[id],trTicket.[manager],'PFM' as SystemSource
                    ,allocationRow.accountNumber,allocationRow.unitPrice
                FROM myTbl AS trTicket 
                LEFT JOIN myAllocation AS allocationRow ON allocationRow.trade_ticket_id=trTicket.id
                    WHERE trTicket.ID = 8779631
                    ORDER BY trTicket.id,allocationRow.AccountNumber
                FOR XML AUTO, type)
            ,
            (
                SELECT trTicket.[id],trTicket.[manager],'CRD' as SystemSource
                    ,allocationRow.accountNumber,allocationRow.unitPrice
                FROM ABC_myTbl AS trTicket 
                LEFT JOIN ABC_myAllocation AS allocationRow ON allocationRow.trade_ticket_id=trTicket.id
                    WHERE trTicket.ID = 8
                    ORDER BY trTicket.id,allocationRow.AccountNumber
                FOR XML AUTO, type)
    FOR XML PATH('trTickets'), ELEMENTS) AS XMLResult

This is the current result:

This is the desired result that I am looking for:

Data sample:

Table ABC_myTbl:

ID  Manager
-----------
8   DOYLE

Table ABC_myAllocation:

accountNumber   unitPrice
-------------------------
F11             100
F22             200

Table myTbl:

ID      Manager
--------------- 
8779631 MCM

Table myAllocation:

accountNumber   unitPrice
--------------------------
292             300

DDL for the tables and their data:

CREATE TABLE dbo.ABC_myTbl 
(
    ID INT  NOT NULL,
    MANAGER VARCHAR(10) NOT NULL
)

CREATE TABLE dbo.myTbl 
(
    ID INT  NOT NULL,
    MANAGER VARCHAR(10) NOT NULL
)   

CREATE TABLE dbo.ABC_myAllocation
(
    accountNumber VARCHAR(10) NOT NULL,
    unitprice     NUMERIC(10, 3) NOT NULL
)

CREATE TABLE dbo.myAllocation
(
    accountNumber VARCHAR(10) NOT NULL,
    unitprice     NUMERIC(10, 3)  NOT NULL
)

INSERT INTO dbo.ABC_myTbl VALUES (8,'DOYLE')

INSERT INTO dbo.ABC_myAllocation VALUES ('F11',100)
INSERT INTO dbo.ABC_myAllocation VALUES ('F22',200)

INSERT INTO dbo.myTbl VALUES (8779631,'MCM')

INSERT INTO dbo.myAllocation  VALUES  ('292',300)

Yitzhak Khabinsky · Accepted Answer

I didn't wait for your DDL and sample data population. So I created a conceptual sample for you. Please pay attention that the tables have implied relationships and they are used in the WHERE clauses.

SQL

-- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT PRIMARY KEY, Manager VARCHAR(20));
INSERT INTO @tbl1 (ID, Manager) VALUES
(8, 'DOYLE'),
(9, 'XYZ');

DECLARE @tbl1Child TABLE (accountNumber CHAR(3) PRIMARY KEY, ParentID INT, unitPrice DECIMAL(10,2));
INSERT INTO @tbl1Child (accountNumber, ParentID, unitPrice) VALUES
('F11', 8, 100)
,('F22', 8, 200)
,('F70', 9, 770);

DECLARE @tbl2 TABLE (ID INT PRIMARY KEY, Manager VARCHAR(20));
INSERT INTO @tbl2 (ID, Manager) VALUES
(8779631, 'MCM')
,(8779555, 'TTT');

DECLARE @tbl2Child TABLE (accountNumber CHAR(3) PRIMARY KEY, ParentID INT, unitPrice DECIMAL(10,2));
INSERT INTO @tbl2Child (accountNumber, ParentID, unitPrice) VALUES
('292', 8779631, 300)
,('255', 8779555, 500);
-- DDL and sample data population, end

SELECT TOP(1) NULL
, (
    SELECT *
        , (
            SELECT * FROM @tbl1Child AS c
            WHERE p.ID = c.ParentID
            FOR XML PATH('allocation_row'), TYPE
        ) 
    FROM @tbl1 AS p
    FOR XML PATH('tradeTicket'), TYPE
)
, (
    SELECT *
        , (
            SELECT * FROM @tbl2Child AS c
            WHERE p.ID = c.ParentID
            FOR XML PATH('allocation_row'), TYPE
    ) 
    FROM @tbl2 AS p
    FOR XML PATH('tradeTicket'), TYPE
)
FROM @tbl1
FOR XML PATH(''), TYPE, ROOT('tradeTickets');

Output

How to combine two FOR XML AUTO into 1 XML?

Answers (1)

Related Questions