Convert simple XML to Table in SQL

Question

I'm trying to convert the following XML to table.

DECLARE @XMLToParse  XML;
SET @XMLToParse =   '<Employees>
                        <Senior>
                            <Emp>
                                Ravi
                            </Emp>
                            <Emp>
                                Shanker
                            </Emp>
                            <Emp>
                                Aditya
                            </Emp>
                        </Senior>
                        <Junior>
                            <Emp>
                                Roy
                            </Emp>
                            <Emp>
                                Sham
                            </Emp>
                            <Emp>
                                Divya
                            </Emp>
                        </Junior>
                      </Employees>';


DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(100),Junior VARCHAR(100))

INSERT
INTO    @ParsingTable
    (Senior)
SELECT  xmlData.A.value('.', 'VARCHAR(100)') AS Senior

FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlData(A)

select * from @ParsingTable

I'm trying to create the table as shown below :

Senior     Junior
-----------------
Ravi       Roy
Shanker    Sham
Aditya     Divya

The above code gives me the first column but I can't figure out how to insert the second col.

Answer 1

I am sure there is lots of way to get your desired result one of them by using cross apply METHOD1

DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(100),Junior VARCHAR(100))

INSERT
INTO    @ParsingTable
    (Senior,Junior)
SELECT  A.value('.', 'VARCHAR(100)') AS Senior,
        B.value('.', 'VARCHAR(100)') AS Junior
FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlDataSenior(A) CROSS APPLY
        @XMLToParse.nodes('Employees/Junior/Emp') as xmlDataJunior(B)

Note: This will give you the duplicate records

METHOD2: From this method you can only get one record ... For first record [1],for second record [2] ,third records [3] and so on..............

SELECT  
    @XMLToParse.value('(Employees/Senior/Emp/text())[1]','VARCHAR(100)') AS Senior,
    @XMLToParse.value('(Employees/Senior/Emp/text())[1]','VARCHAR(100)') AS Junior

METHOD3: You can get your desired result by this trick

Make 2 Common Table Expression one for Senior and one for Junior with Row_Number and join these two ctes by row_number of first cte with second cte Here is Your Complete Code

     DECLARE @XMLToParse  XML;
     SET @XMLToParse =   '<Employees>
                        <Senior>
                            <Emp>
                                Ravi
                            </Emp>
                            <Emp>
                                Shanker
                            </Emp>
                            <Emp>
                                Aditya
                            </Emp>
                        </Senior>
                        <Junior>
                            <Emp>
                                Roy
                            </Emp>
                            <Emp>
                                Sham
                            </Emp>
                            <Emp>
                                Divya
                            </Emp>
                        </Junior>
                      </Employees>';


DECLARE @ParsingTable  TABLE
    (Senior VARCHAR(1000),Junior VARCHAR(100))


;with cte as
(
SELECT  A.value('.', 'VARCHAR(100)') AS Senior,
        ROW_NUMBER() OVER(ORDER BY  A.value('.', 'VARCHAR(100)') DESC) AS SeniorRowNo

FROM    @XMLToParse.nodes('Employees/Senior/Emp') as xmlDataSenior(A)
)

 , cte2 as
 ( 
        SELECT  B.value('.', 'VARCHAR(100)') AS Junior,
        ROW_NUMBER() OVER(ORDER BY  B.value('.', 'VARCHAR(1000)') DESC) AS JuniorRowNo
         FROM
        @XMLToParse.nodes('Employees/Junior/Emp') as xmlDataJunior(B)   
 )

 INSERT INTO    @ParsingTable(Senior,Junior)
 Select cte.Senior,cte2.Junior
 From cte inner join cte2 on cte.SeniorRowNo= cte2.JuniorRowNo
 select * from @ParsingTable

SQLFIDDLE METHOD 3

OUTPUT:

  Senior     Junior
-----------------
Shanker    Sham
Ravi       Roy
Aditya     Divya

Answer 2

Pivot looks like the obvious thing to do here but it requires some perhaps not so obvious XML queries to get a result set that you can do the pivot over.

select P.Senior, 
       P.Junior
from (
     select T1.X.value('local-name(.)', 'nvarchar(100)') as ColumnName,
            T3.Value,
            T3.RowID
     from @XMLToParse.nodes('/Employees/*') as T1(X)
       cross apply (
                   select T2.X.value('text()[1]', 'nvarchar(100)') as Value,
                          row_number() over(order by T2.X) as RowID
                   from T1.X.nodes('Emp') as T2(X)
                   ) as T3
     ) as T
pivot (max(T.Value) for T.ColumnName in (Senior, Junior)) as P

The derived table that you pivot over looks like this:

ColumnName Value      RowID
---------- ---------- --------------------
Senior     Ravi       1
Senior     Shanker    2
Senior     Aditya     3
Junior     Roy        1
Junior     Sham       2
Junior     Divya      3

RowID is what is pairing the rows together.