Parse XML and generate new rows through SQL Query

Question

I've the input data in SQL table in below format:

ID    Text
 1    <Key><Name>Adobe</Name><Display>Ado</Display></Key><Key>.....</Key>
 2    <Key><Name></Name><Display>Microsoft</Display><Version>1.1</Version></Key>

There can be multiple keys for each ID.There could be several thousand rows in a table in above format. I've to generate the final sql output in below format

ID  Name  Display    Version
1   Adobe Ado       
1   xyz   yz         1.2
2         Microsoft  1.1

I am using the below query to parse Text column, but getting all data in one row. How can I split that data in multiple rows as indicated above.

SELECT 
    CAST(CAST(Text AS XML).query('data(/Key/Name)') AS VARCHAR(MAX)) AS Name,
    CAST(CAST(Text AS XML).query('data(/Key/Display)') as VARCHAR(MAX)) AS DisplayName,
    CAST(CAST(Text AS XML).query('data(/Key/Version)') AS VARCHAR(MAX)) AS Version
FROM 
    ABC where ID = 1

Currently I am running this query for each ID at a time. Is there a way to run for all ID's together. Also, is there any other efficient way to get the desired output.

Roger Wolf · Accepted Answer · 2016-08-13 13:30:25Z

1

Here is the example:

-- Sample demonstrational schema
declare @t table (
    Id int primary key,
    TextData nvarchar(max) not null
);

insert into @t
values
(1, N'<Key><Name>Adobe</Name><Display>Ado</Display></Key><Key><Name>xyz</Name><Display>yz</Display><Version>1.2</Version></Key>'),
(2, N'<Key><Name></Name><Display>Microsoft</Display><Version>1.1</Version></Key>');

-- The actual query
with cte as (
    select t.Id, cast(t.TextData as xml) as [XMLData]
    from @t t
)
select c.Id,
    k.c.value('./Name[1]', 'varchar(max)') as [Name],
    k.c.value('./Display[1]', 'varchar(max)') as [DisplayName],
    k.c.value('./Version[1]', 'varchar(max)') as [Version]
from cte c
    cross apply c.XMLData.nodes('/Key') k(c);

Different type can be corrected with the in-place cast/convert done in CTE (or equivalent subquery).

edited Aug 13, 2016 at 13:30

answered Aug 12, 2016 at 7:59

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anks Over a year ago

What if column containing XML Data is not xml type ? In my case, column containing XML Data is nvarchar type, so I get error as "The XMLDT method 'nodes' can only be invoked on columns of type xml"

Collectives™ on Stack Overflow

Parse XML and generate new rows through SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related