Item Index for all items in nested XML query

Question

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.

Fiddle here with sample data and working 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?

Answer 1

I found the answer - I needed to create a CTE that uses a union of all my child and parent records and creates a ROW_NUMBER(), then JOIN to that CTE to get the ROW_NUMBER() value which will be unique across all records.

Solution fiddle here.

Full solution to paste into SSMS:

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)

;WITH IDs AS
(
    SELECT
       RN = (ROW_NUMBER() OVER (ORDER BY  RecId,CASE WHEN ChildId IS NULL THEN 0 ELSE 1 END) -1),
       RecId,
       ChildId
    FROM
       (
       SELECT
          RecId, ChildId = NULL
       FROM
          #Parent
       UNION ALL
       SELECT
          RecId, ChildId
       FROM 
          #Parent P
       INNER JOIN
          #Child C
              ON C.ParentRecId = P.RecId) x
)

SELECT
  P.RecId as 'RID',
  P.PersonName as 'PNAM',
  P.Age,
  I.RN as 'Index',
  (
    SELECT 
      C.SalesAmt as 'SAMT',
      I.RN as 'Index'
    FROM
      #Child C
    INNER JOIN
       IDs I
          ON I.ChildId = C.ChildID
    WHERE
      C.ParentRecId = P.RecId
    FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE
  )
FROM
  #Parent P
INNER JOIN
    IDs I
       ON I.RecId = P.RecId
       AND I.ChildId IS NULL
FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE