How to edit XML values using SQL queries?

Question

I have an XML column UserDetails in a SQL Server database which has value like this:

<fleet>
    <Setting name="city"/>
    <UserSettings name="details">
        <UserSettings name="A1">
            <setting type="1" name="email"/>
            <setting value="user"/>
        </UserSettings>
        <UserSettings name="A2">
            <setting type="2" name="phone"/>
            <setting value="technician"/>
        </UserSettings>
    </UserSettings>     
</fleet>

I would to read from the db and add a new setting in

<UserSettings name="A2"> .

Can someone point me to what I should be doing?

Here is a good place to start

Zohar Peled
– Zohar Peled

2017-08-10 06:15:36 +00:00
Commented Aug 10, 2017 at 6:15 — Zohar Peled
– Zohar Peled, Commented Aug 10, 2017 at 6:15

Gottfried Lesigang · Accepted Answer · 2017-08-17 11:02:36Z

You might use sql:variable() to introduce a value you've created externally. And you can use Xml.exist() to find XMLs which haven't got this new entry before:

DECLARE @myTbl TABLE(Descr VARCHAR(100),TheXml XML);            
INSERT INTO @myTbl VALUES
(   
'Setting "A3" doesn''t exist','<fleet>
    <Setting name="city" />
    <UserSettings name="details">
        <UserSettings name="A1">
            <setting type="1" name="email" />
            <setting value="user" />
        </UserSettings>
        <UserSettings name="A2">
            <setting type="2" name="phone" />
            <setting value="technician" />
        </UserSettings>
    </UserSettings>
</fleet>'
)
,(   
'Setting "A3" exists already','<fleet>
    <Setting name="city" />
    <UserSettings name="details">
        <UserSettings name="A1">
            <setting type="1" name="email" />
            <setting value="user" />
        </UserSettings>
        <UserSettings name="A2">
            <setting type="2" name="phone" />
            <setting value="technician" />
        </UserSettings>
        <UserSettings name="A3">
            <setting type="3" name="A3 setting" />
            <setting value="this exists already" />
        </UserSettings>
    </UserSettings>
</fleet>'
);  

DECLARE @NewSetting XML=
      N'<UserSettings name="A3">
            <setting type="3" name="new A3" />
            <setting value="new data" />
        </UserSettings>';


UPDATE @myTbl
SET TheXml.modify('             
insert sql:variable("@NewSetting")
as last into (/fleet/UserSettings)[1]')           
WHERE TheXml.exist(N'/fleet/UserSettings[@name="details"]/UserSettings[@name="A3"]')=0;  

SELECT * FROM @myTbl

Zohar Peled · Accepted Answer · 2017-08-10 06:50:42Z

1

SQL SERVER provides methods to modify XML data, and a language to do it (XML DML).
You need to use the modify method with an insert xml dml statement.

Here is the complete example:

Sample data:

DECLARE @myDoc xml;            
SET @myDoc =   
'<fleet>
    <Setting name="city" />
    <UserSettings name="details">
        <UserSettings name="A1">
            <setting type="1" name="email" />
            <setting value="user" />
        </UserSettings>
        <UserSettings name="A2">
            <setting type="2" name="phone" />
            <setting value="technician" />
        </UserSettings>
    </UserSettings>
</fleet>';

The statement:

SET @myDoc.modify('             
insert <setting value="NEW SETTINGS" />
as first into (/fleet/UserSettings/UserSettings[@name="A2"])[1] ') ;           
SELECT @myDoc;

Result:

<fleet>
    <Setting name="city" />
    <UserSettings name="details">
        <UserSettings name="A1">
            <setting type="1" name="email" />
            <setting value="user" />
        </UserSettings>
        <UserSettings name="A2">
            <setting value="NEW SETTINGS" />
            <setting type="2" name="phone" />
            <setting value="technician" />
        </UserSettings>
    </UserSettings>
</fleet>

answered Aug 10, 2017 at 6:50

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

2 Comments

user1687824 Over a year ago

Thanks for the explanation ..Is it not possible to get the column value as xml and insert the new setting if its not found? ...

Zohar Peled Over a year ago

I guess there is, but I don't know how. I've answered your question mainly because it was an opportunity for me to learn more about SQL Server's XML support. I suggest using the links I gave you both in the comment and in the answer and learn, just like I did.

Collectives™ on Stack Overflow

How to edit XML values using SQL queries?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related