Convert plain XML to table in SQL
I am trying to convert the following XML to a 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 am trying to create a table like below:
Senior Junior
-----------------
Ravi Roy
Shanker Sham
Aditya Divya
The above code gives me the first column, but I cannot figure out how to insert the second column.
I'm sure there are many ways to get the desired result from one of them 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 duplicate entries
method2:
From this method you can only get one record ... For the first record [1]
, for the second record [2]
, third records [3]
, etc ...............
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 the desired result with this trick
Make 2 Common Table Expressions one for Senior and one for Junior with Row_Number
and join these two cte
to the row_number of the first cte with the 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
Pivot looks like an obvious thing to do here, but it requires some perhaps not so obvious XML queries to get a result set that you can pivot into.
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 you pivot 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 connects strings together.