insert xml data to table stored procedure sql server

Question

I have xml in following format

 <Entity>
    <name>John</name>
    <aliases><alias>Johnny</alias></aliases>
    <aliases><alias>Johnson</alias></aliases>
    </Entity>
    <Entity>
    <name>Smith</name>
    <aliases><alias>Smithy</alias></aliases>
    <aliases><alias>Schmit</alias></aliases>
    </Entity>

I want to insert them in table so the table should have 4 records in the example.

columns are name and alias.

name | alias
John | Johnny
John | Johnson
Smith| Smithy
Smith| Schmit

How can I achive this using cursor or something else?

What i have tried. In cursor for entity i try insert alias value,but only first alias is taken.

insert into  TESTTABLE
            (EntityID,Alias)
        select 
            @EntityID as EntityID,
            Alias
        from OpenXml(@ixml, '/Aliases',2)
        with (
            Alias varchar(255) '.'
        )

@gaurav thank you for the reference but because the structure is <aliases><alias> for each alias it is not working for me — xMilos
– xMilos, Commented May 28, 2018 at 8:48

Rajat Jaiswal · Accepted Answer · 2018-05-28 08:43:47Z

2

DECLARE @XML AS XML= N'
<Entity>
    <name>John</name>
    <aliases><alias>Johnny</alias></aliases>
    <aliases><alias>Johnson</alias></aliases>
    </Entity>
    <Entity>
    <name>Smith</name>
    <aliases><alias>Smithy</alias></aliases>


     <aliases><alias>Schmit</alias></aliases>
        </Entity>'

INSERT INTO @tblTest(firstName,LastName)
        SELECT  t1.c.value('../name[1]','varchar(100)') As FirstName,t1.c.value('alias[1]','varchar(50)') as SecondName

    FROM @xml.nodes('/Entity/aliases') t1(c)

answered May 28, 2018 at 8:43

Rajat Jaiswal

6414 silver badges15 bronze badges

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

5 Comments

xMilos Over a year ago

I will try, I have to mention that there can be any number of alias 0 to n

xMilos Over a year ago

<aliases><alias>Smithy</alias><alias>Schmit</alias></aliases> if i have aliases with multiple alias it gets only the first one, how can also include this in the solution, otherwise this answer is correct for the example.

xMilos Over a year ago

alias[1] can be replaced with .

Gottfried Lesigang Over a year ago

In general it is not a good idea to hop back (../name[1])... And it's faster to use (somenode/text())[1] instead of somenode[1]. You migtht want to read this related answer

Gottfried Lesigang Over a year ago

@MasterYi Your trick to replace alias[1] with a simple . works in this very simple case, but is a dangerous and not recommended approach. The general advise is: Be as specific as possible

Gottfried Lesigang · Accepted Answer · 2020-05-28 07:52:42Z

First of all: Your XML is not well-formed as it is missing a root node. SQL-server can deal with XML-fragments, but other engines might fail...

You have a 1:n related two level hierarchy. This is best queried with a cascade of .nodes():

DECLARE @XML AS XML= 
N'<Entity>
    <name>John</name>
    <aliases>
    <alias>Johnny</alias>
    </aliases>
    <aliases>
    <alias>Johnson</alias>
    </aliases>
</Entity>
<Entity>
    <name>Smith</name>
    <aliases>
    <alias>Smithy</alias>
    </aliases>
    <aliases>
    <alias>Schmit</alias>
    </aliases>
</Entity>'

--the query will use .nodes() to get all entities and a second time .nodes() to get a derived table of the aliases below each entity.

SELECT  A.ent.value('(name/text())[1]','varchar(100)') As FirstName
       ,B.ali.value('(alias/text())[1]','varchar(50)') as SecondName
FROM @xml.nodes('/Entity') A(ent)
CROSS APPLY A.ent.nodes(N'aliases') AS B(ali);

UPDATE added attributes

According to one comment below I changed this a bit to reflect multiple aliases with an attribute.

DECLARE @XML AS XML= 
N'<Entity>
    <name>John</name>
    <aliases>
    <alias nr="1">Johnny</alias>
    <alias nr="2">OneMore</alias>
    </aliases>
    <aliases>
    <alias>Johnson</alias>
    </aliases>
</Entity>
<Entity>
    <name>Smith</name>
    <aliases>
    <alias nr="1">Smithy</alias>
    </aliases>
    <aliases>
    <alias nr="1">Schmit</alias>
    </aliases>
</Entity>' 

SELECT  A.ent.value('(name/text())[1]','varchar(100)') As FirstName
       ,B.ali.value('@nr','int') as SomeAttribute
       ,B.ali.value('text()[1]','varchar(50)') as SecondName
FROM @xml.nodes('/Entity') A(ent)
CROSS APPLY A.ent.nodes(N'aliases/alias') AS B(ali);

What has changed?

.nodes() is diving one level deeper to /alias, thus repeating multiple aliases in multiple rows.
reading the attribute with a leading @

Thank you! Great and simple solution. One Question what if alias would have an Attribute (like : <alias nr="1">Schmit</alias>) can i read it too the same way?

Collectives™ on Stack Overflow

insert xml data to table stored procedure sql server

2 Answers 2

5 Comments

UPDATE added attributes

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

UPDATE added attributes

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related