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 for each alias it is not working for me — xMilos, May 28 '18 at 08:48

score 2 · Accepted Answer · answered May 28 '18 at 08:43

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 '18 at 08:43

Rajat Jaiswal

645
4
15

I will try, I have to mention that there can be any number of alias 0 to n – xMilos May 28 '18 at 08:48
SmithySchmit 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 May 28 '18 at 09:22
alias[1] can be replaced with . – xMilos May 28 '18 at 09:29
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](https://stackoverflow.com/a/43242238/5089204) – Shnugo May 28 '18 at 13:16
@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* – Shnugo May 28 '18 at 13:40

Shnugo · Answer 2 · 2020-05-28T07:52:42.113

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 : Schmit) can i read it too the same way? — Alexander S., May 27 '20 at 20:34

insert xml data to table stored procedure sql server

2 Answers2

UPDATE added attributes

Linked