CODEWITHSUNDEEP
sql-serverxml-parsing
Anks
Anks

Reputation: 11

Parse XML and generate new rows through SQL Query

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.

Upvotes: 1

Views: 617

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

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).

Upvotes: 1

Related Questions