Transform XML into table variable

Question

Can anyone please show me how to turn the following XML string into a table variable with the following structure:

DECLARE @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<skillsProfilesTargets>
    <skill ID="1">
        <profile ID="50" targetLevel="75" />
        <profile ID="60" targetLevel="75.00"/>
        <profile ID="70" targetLevel="60.00"/>
    </skill>
    <skill ID="2">
        <profile ID="50" targetLevel="75" />
        <profile ID="60" targetLevel="50.00"/>
    </skill>
    <skill ID="3">
        <profile ID="50" targetLevel="" />
        <profile ID="60" targetLevel="75"/>
        <profile ID="90" targetLevel="60.00"/>
    </skill>
    <skill ID="4">
        <profile ID="50" targetLevel="" />
        <profile ID="60" targetLevel="75"/>
        <profile ID="100" targetLevel="75"/>
        <profile ID="250" targetLevel="100"/>
    </skill>
</skillsProfilesTargets>'


SkillID profileID   targetLevel
-----------------------------------
1       50      75
1       60      75
1       70      60
2       50      60
2       60      50
3       50      NULL
3       60      75

I've been looking at a number of tutorials online, but they all assume each 'skill' node has the same number of child nodes.

Thanks in advance.

Kevin Suchlicki · Accepted Answer · 2013-05-03 16:30:43Z

3

Here is another solution which uses XQuery instead of sp_xml_preparedocument (read up on the pros and cons of each method):

SELECT  Skill.n.value('@ID', 'int') AS SkillID
        , Profile.n.value('@ID','int') AS profileID
        , Profile.n.value('@targetLevel','varchar(20)') AS targetLevel
FROM    @xml.nodes('/skillsProfilesTargets/skill') AS Skill(n)
        CROSS APPLY Skill.n.nodes('profile') AS Profile(n);

answered May 3, 2013 at 16:30

Kevin Suchlicki

3,1542 gold badges17 silver badges17 bronze badges

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

2 Comments

EvilDr Over a year ago

Just out of interest, why must @targetLevel be cast as varchar(20) instead of numeric(5,2) or decimal? It errors out of I try casting to a numeric format

Kevin Suchlicki Over a year ago

Hey EvilDr, it's because you can't cast empty string to numeric type. Try SELECT CAST('' as numeric(19,5)) and you will get the same error. You need to amend your XML to include zero instead of blank, or else do something like the following to change blanks into NULLs: CAST(NULLIF(Profile.n.value('@targetLevel','varchar(20)'),'') AS NUMERIC(19,5)) AS targetLevel

Kenneth Fisher · Accepted Answer · 2013-05-03 16:26:04Z

The only problem I ran into was that targetLevel had spaces in some cases which meant I couldn't use a decimal(10,2). So your output on targetLevel has blanks instead of NULLs. If you remove those properties and change the targetLevel in the with clause from varchar(10) to decimal(10,2) then it will give you exactly the output you want.

Here is a sample of what I mean by removing the targetLevel.

    <skill ID="3">
        <profile ID="50"/>
        <profile ID="60" targetLevel="75"/>
        <profile ID="90" targetLevel="60.00"/>
    </skill>

Below here is the answer using your existing XML. There may be a way to force the spaces to show up as a NULL, I just don't know it. Oh, and of course you can do a SELECT * INTO @tablevar to put the data into the table var.

DECLARE @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<skillsProfilesTargets>
    <skill ID="1">
        <profile ID="50" targetLevel="75" />
        <profile ID="60" targetLevel="75.00"/>
        <profile ID="70" targetLevel="60.00"/>
    </skill>
    <skill ID="2">
        <profile ID="50" targetLevel="75" />
        <profile ID="60" targetLevel="50.00"/>
    </skill>
    <skill ID="3">
        <profile ID="50" targetLevel="" />
        <profile ID="60" targetLevel="75"/>
        <profile ID="90" targetLevel="60.00"/>
    </skill>
    <skill ID="4">
        <profile ID="50" targetLevel="" />
        <profile ID="60" targetLevel="75"/>
        <profile ID="100" targetLevel="75"/>
        <profile ID="250" targetLevel="100"/>
    </skill>
</skillsProfilesTargets>'

declare @hxml int

EXEC sp_xml_preparedocument @hxml OUTPUT, @xml

SELECT *
FROM OPENXML(@hXML,'/skillsProfilesTargets/skill/profile',3)
WITH (SkillID INT '../@ID', 
    profileID INT '@ID',
    targetLevel varchar(10))

EXEC sp_xml_removedocument @hxml

Great thanks that's excellent. I amended it slightly to use CASE to force NULLs back into the results: <code>SELECT SkillID, profileID, CASE WHEN targetLevel = '' THEN NULL ELSE targetLevel END as [targetLevel] FROM OPENXML(....)</code>

Collectives™ on Stack Overflow

Transform XML into table variable

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related