Home » Microsoft TechnologiesRSS

for xml + xml with 2 root elements

we are using sql server 2005 and I need to generate xml out of stored procedure which has 2 select queries. The xml should look like this:

<aaa:Requestxmlns:aaa="aaa:import:request:1.0"timestamp=""id="Bob2">

        <aaa:Request1>

            <aaa:Transactions>

                <aaa:Transactionid="Bob3">

                    <aaa:Source></qed:Source>

                </aaa:Transaction>

            </aaa:Transactions>

        </aaa:Request1>

</aaa:Request>

    <bbb:Instructionsxmlns:bbb="aaa:import:request:bbb:1.0">

        <bbb:Instructionid="bob1">

 

        </bbb:Instruction>

    </bbb:Instructions>

 The select query I am using

WITH XMLNAMESPACES('aaa:import:request:1.0' as "aaa", 'bbb:import:request:bbb:1.0' as "bbb")

select
(
SELECT
(
 SELECT 'DEFAULT' AS "@on-linked-transaction-failure",
 ( 
  SELECT 'p' + Convert(varchar(10),TransactionInstructionExtractID) as "@id",
    'hello' AS "aaa:Source"
        
   FROM Table1
   WHERE EDateTime IS NULL
   FOR XML PATH('aaa:Transaction'), type
 )
 FOR XML PATH('aaa:Transactions'),type
 )
FOR XML PATH('aaa:Request1'),type

)
,
  (SELECT DISTINCT(TxID) AS "@id",
   TxID as "bbb:ResourceID"
    
 FROM  Table2
 FOR XML PATH('bbb:Instruction'),
 ROOT('bbb:Instructions'), type)

FOR XML PATH ('Request')

<aaa:Requestxmlns:aaa="aaa:import:request:1.0"timestamp=""id="Bob2">

        <aaa:Request1>

            <aaa:Transactions>

                <aaa:Transactionid="Bob3">

                    <aaa:Source></qed:Source>

                </aaa:Transaction>

            </aaa:Transactions>

        </aaa:Request1>

    <bbb:Instructionsxmlns:bbb="aaa:import:request:bbb:1.0">

        <bbb:Instructionid="bob1">

             ...........

        </bbb:Instruction>

    </bbb:Instructions>

</aaa:Request>

My xml is under 1 root element 'Request'. I want 2 root elements like <Request> </Request> and <Instructions></Instructions>. when with namespaces is used, shows name space for each row. I cleaned all that in the above xml. So, my question is is it possible to have 2 root elements as 1 xml from one stored procedure with 2 sql queries. If there is no way then, I need to use 2 stored procs, get xml and combine them on front end (c#) and make 1 xml document.

I appreciate your response.

Thanks,

Spunny

 

 

 

2 Answers Found

 

Answer 1

CREATE TABLE Table1(TransactionInstructionExtractID INT, EDateTime DATETIME2)
CREATE TABLE Table2(TxID INT)

INSERT Table1 (TransactionInstructionExtractID) VALUES (1), (2), (3)
INSERT Table2 VALUES (1), (2), (3)

;
WITH XMLNAMESPACES('aaa:import:request:1.0' as "aaa", 'bbb:import:request:bbb:1.0' as "bbb")
SELECT
(
	SELECT
	(
		SELECT
		(
			SELECT
			(
				SELECT	'p' + Convert(varchar(10),TransactionInstructionExtractID) as "@id"
					,	'hello' AS "aaa:Source"
				FROM Table1 
				WHERE EDateTime IS NULL 
				FOR XML PATH('aaa:Transaction'), TYPE
			)
			FOR XML PATH('aaa:Transactions'), TYPE
		)
		FOR XML PATH('aaa:Request1'), TYPE
	)
	FOR XML PATH('aaa:Request'), TYPE
),
(
	SELECT DISTINCT TxID AS "@id", TxID as "bbb:ResourceID"
  FROM Table2
  FOR XML PATH('bbb:Instruction'), ROOT('bbb:Instructions'), TYPE
)
FOR XML PATH('')

DROP TABLE Table1
DROP TABLE Table2
 

Answer 2

Thanks polish. I will run the query and in case of problems, I will post again.

 

Thanks,

Spunny

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter