I have a requirement to build an XML package with a parent record, and 1..N related child records.
The child records are to be a nodes under the parent node, and there may be one child record or several for each parent. It's also possible to have multiple parents in the same query.
The above query I have with no issue.
I also have a requirement to give each record (parent and child) a unique 0-based index. I can do with with
ROW_NUMBER for each subquery, but can't figure out a way to get a unique index for every record in the entire query.
(Note, SQLFiddle does not display XML in a very easy to read format so you may want to run this in your local SSMS.)
Working SSMS code below:
BEGIN TRY DROP TABLE #Child DROP TABLE #Parent END TRY BEGIN CATCH END CATCH CREATE TABLE #Parent (RecId int PRIMARY KEY NOT NULL, PersonName varchar(100), Age int) CREATE TABLE #Child (ChildID int identity PRIMARY KEY NOT NULL, ParentRecId int FOREIGN KEY REFERENCES #Parent(RecId), SalesAmt money) INSERT INTO #Parent (RecID, PersonName, Age) VALUES (1, 'Aaron Bertrand', 99), (2, 'Paul White', 20), (3, 'JNK', 33) INSERT INTO #Child (ParentRecID, SalesAmt) VALUES (1, 10.00), (1, 20.00), (2, 15.15), (2, 100.00), (3, 0.00) SELECT RecId as 'RID', PersonName as 'PNAM', Age, ( SELECT C.SalesAmt as 'SAMT', (ROW_NUMBER() OVER (ORDER BY ParentRecId) - 1) as 'Index' FROM #Child C WHERE C.ParentRecId = P.RecId FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE ) FROM #Parent P FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE
How can I get a unique index across all parent and child records for this XML package?