MS SQL Update for a metadata column as text field

I am using SQL Server to create a query(update statement)

The update statement is against a data  type of text.

Within the text is xml data.

Here is what I need to do.

I have some 3000 records that sometimes the xml tag is there and soemtimes its not.

I need to be able to add about 5 xml tags to each row in this field called msettings.
If the tag inside of msettings exists I need to be able to either override it or keep it the same.   It would be nice to construct the query so that could be optional depending on the situation.
And then ofcourse if the tag does not exist I would want it to append to the xml.  I do not think order matters as these are just tags...so its more like metadata

So

update table
set msettings =
where something = something


Please help...im not sure if I should be doing xpath...or some other logic.
I was hoping to make a robust solution as I could see reusing this later
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
Do you know where exactly you  need to add the new tags within the xml ? If there is some specific tag that you can append after then you can potentially use replace.
Robb HillSenior .Net DeveloperAuthor Commented:
no..its not that logical....

1)  I will need to replace or ignore if tag exists.   That logic may vary...but we could go with replace there.
2)  If the tag is not in the sql field...I need to add it.  I dont think order matters here for the purpose of this program...i just would not want to add a tag with the same name.  This is  just a text field using xml data..so it would let me do that if I tried..which would be an error
Ares KurkluSoftware EngineerCommented:
The only thing i can think you can do with sql statements is to cast the text to xml and search for specific tags within xml with "exists" and run updates on those subsets. e.g if <abc> tag doesn't exist add that etc.. you can search root tags and other tags:


SELECT
 *
FROM
(
    SELECT
        CAST([content] AS XML) AS xml
    FROM
        table_name
) test
WHERE
xml.exist(<search here>) = 1

more info here:

https://docs.microsoft.com/en-us/sql/t-sql/xml/exist-method-xml-data-type
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robb HillSenior .Net DeveloperAuthor Commented:
would I have to not loop through the tags....checking..updaing and then appending...does that logic work in a statement......or with xpath versus standard sql
Ares KurkluSoftware EngineerCommented:
If you know exactly what tag you are looking for like /abc/xyz you can check if xyx exists or check if the there is a specific value within the tag by using xml.exist

I guess you will have to write multiple queries for things you want to replace by doing separate checks.
Robb HillSenior .Net DeveloperAuthor Commented:
hmmm.....im not sure.


So are you saying for every tag ..I would need a new query?
Ares KurkluSoftware EngineerCommented:
well yes i guess so if you want to create  /abc/xyz you need to check and create it,  if you want to create another one you will need another check for that there will be multiple update queries based on the specific where clauses.

it is hard if it is not that logical it is harder to do with just sql may be better to write a program.
Robb HillSenior .Net DeveloperAuthor Commented:
oh well I was hoping you would not go there.......I dont think this is helpful....I am back where I began.....could not a cursur do this....what type of program ....net ..powershell....end of day...I have about 3000 customers that cannot do something because I need to get this xml correct....


I like complex...but I a need to get it updated....any other ideas?
Ares KurkluSoftware EngineerCommented:
Well i think a cursor can do it as well, i guess what you are trying to do is a bit too complex for a single query, again I am not clear about all your requirements, it somehow has to be well defined but you can always have a SP which checks the text field, then processes it and applies the changes, or at least it can output some update queries. If you will have to do this only once you may as well write separate queries.

would be interesting to see other ideas.
Mark WillsTopic AdvisorCommented:
Bit hard to visualise and a lot of esoteric might have tags - or not; might need to change - or not; might need to add - or not.

Is it possible to see some samples along with expectations of result ?
Robb HillSenior .Net DeveloperAuthor Commented:
I cAN GIVE YOU A TAG...BUT ONCE AGAIN...ITS A  TEXT FIELD WHER THE ORDER OF TAGS DOES NOT MATTER.

SO YOU HAVE

<TAG1>VALUE><TAG1/)  

This is the structure
Mark WillsTopic AdvisorCommented:
Hmmm...

Can always do
SELECT Nodes.Tags.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Tags)

Open in new window


e.g.
DECLARE @xml as xml = '<TAG1>VALUE1</TAG1> <TAG2>VALUE2</TAG2>'

SELECT Nodes.Tags.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Tags)

Open in new window

Robb HillSenior .Net DeveloperAuthor Commented:
yes
Mark WillsTopic AdvisorCommented:
So, that will give you a method for adding in new tags where not exists (SELECT Nodes.Tags.query('local-name(.)') FROM yourcolumn.nodes('//*') As Nodes(Tags))

And to identify tags (do first) that needs updating... Not sure about what you need to update / replace or if attached to a more specific tag.

So create a cursor and loop through row by row checking the tags (or as a collection), and modify tags then insert new tags as needed as xml in your msettings field, then update msettings in your table.
Mark WillsTopic AdvisorCommented:
Well Rob,

What do you want to do with your question ?

Explore further (the Cursor)
Explain the Update of existing tags

For what its worth, here is a quick example of insert, update, delete
create table #config (id int identity, msettings xml)
go

insert #config values ('<tag1>v1</tag1><tag2>v2</tag2>')
go

update #config set msettings.modify('insert <newtag>3</newtag> into (/.)')
go

update #config set msettings.modify('replace value of (/tag1/text())[1] with ("newvalue")')
go

update #config set msettings.modify('delete (/tag2)')
go

select * from #config

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.