CODEWITHSUNDEEP
sqlsql-server
DigitalCattle
DigitalCattle

Reputation: 1

SQL XML to Table

How to convert this XML to a table in sql server 2008

Input:

<Object>
    <Property Name="Prop1">1_1</Property>
    <Property Name="Prop2">1_2</Property>
    <Property Name="Prop3">1_3</Property>
  </Object>
  <Object>
    <Property Name="Prop1">2_1</Property>
    <Property Name="Prop2">2_2</Property>
    <Property Name="Prop3">2_3</Property>
  </Object>

Output:

Prop1    Prop2    Prop3
1_1      1_2      1_3
2_1      2_2      1_3

Upvotes: 0

Views: 96

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Here is the solution

DECLARE @xml XML = '<Object>
    <Property Name="Prop1">1_1</Property>
    <Property Name="Prop2">1_2</Property>
    <Property Name="Prop3">1_3</Property>
  </Object>
  <Object>
    <Property Name="Prop1">2_1</Property>
    <Property Name="Prop2">2_2</Property>
    <Property Name="Prop3">2_3</Property>
  </Object>
  <Object>
    <Property Name="Prop1">3_1</Property>
    <Property Name="Prop2">3_2</Property>
    <Property Name="Prop3">3_3</Property>
  </Object>'

;WITH CTE
AS
(
  SELECT 
    rn, p.value('(@Name)[1]', 'varchar(50)') Prop, p.value('.', 'varchar(50)') PropVal
  FROM 
  (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) rn,  o.query('.') obj
    FROM (VALUES (@xml)) A(x)
    CROSS APPLY x.nodes('Object') P(o)

  ) O
  CROSS APPLY O.obj.nodes('Object/Property') B(p)
)
SELECT Prop1, Prop2, Prop3
FROM CTE
PIVOT
(
    MIN(PropVal)
    FOR Prop IN (Prop1, Prop2, Prop3)
) piv

with desired output

Prop1   Prop2   Prop3
1_1     1_2     1_3
2_1     2_2     2_3
3_1     3_2     3_3

Upvotes: 1

Related Questions