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.
source to share
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
OUTPUT:
Senior Junior
-----------------
Shanker Sham
Ravi Roy
Aditya Divya
source to share
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.
source to share