September 8, 2011 at 9:01 am
I have many records with an xml column with values like:
record A:
<root>
<apple name="apple" displayname="Apple"><value>goldstar</value></apple>
<building name="building" displayname="Building"><value>flat</value></building>
</root>
record B:
<root>
<apple name="apple" displayname="Apple"><value>goldstar</value></apple>
<car name="car" displayname="Car"><value>Mazda</value></car>
<tree name="tree" displayname="Tree"><value>palm tree</value></tree>
</root>
I want to update these records with the following xml as base but use the values from the current records
<root>
<apple name="apple" displayname="Apple"><value>red apple</value></apple>
<building name="building" displayname="Building"><value>church</value></building>
<tree name="tree" displayname="Tree"><value>oak</value></tree>
</root>
results should be:
record A:
<root>
<apple name="apple" displayname="Apple"><value>goldstar</value></apple>
<building name="building" displayname="Building"><value>flat</value></building>
<tree name="tree" displayname="Tree"><value>oak</value></tree>
</root>
record B:
<root>
<apple name="apple" displayname="Apple"><value>goldstar</value></apple>
<building name="building" displayname="Building"><value>church</value></building>
<tree name="tree" displayname="Tree"><value>palm tree</value></tree>
</root>
The name of the root will always be known, but not the child nodes names. Though the construction of those nodes will always be the same.
Anyone to solve this challenge?? tried many ways and read so much articles on this website, that can't see the solution anymore.
September 8, 2011 at 9:10 am
I don't understand what you're trying to update. I can't see how your starting data relates to your ending data.
Updating XML in T-SQL is through the "modify" method. But I can't suggest details since I don't get what the data rules are here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 9, 2011 at 1:47 am
Try to explain a bit more:
I have several hundred records with xml I want to update. And all those records have an old xml format with different elements. Even de elements differs between the records. Due to new functionality those records can be updated whenever the admin wants to with a new format. Elements can be added, deleted or even the sequence can be adjusted within the xml.
Only the values of need to be persist. So I need a stored procedure with the new xml format as input. In the most simple way i will get this:
create procedure updatexml
@xml xml
as
update table set xmlcolumn = @xml
But only this @xml needs the values of the old values of the xmlcolumn. I understood that with modify you only can change 1 item in your xml. But I need more updates within the xml per row. And I want that in one statement without fetching the rows or do some stuff in .NET. I could do some xsl transform, but i need to install some assemblies to get this right, but we are not in control to install those additional software stuff.
So back to my first post:
@xml has an element apple with value red apple and also record A has an element apple but with value goldstar. => so red apple needs to be replaced with goldstar. and for element 'building' the value 'church' needs to be replaced with 'flat'. Element 'tree' doesn't exist in record A and can keep it's default value 'oak'. For record B the elements 'apple' and tree' also exist in @xml. So the values of those elements needs to be persisted. In the result records the updated values are bold and the records are all in the format of @xml.
So in antoher way I have to go through the elements of the parameter and find the elements in all the records and get out the values and put those in the new xml that will be updated in the record again.
I hope this post explains the situation a bit better.
September 9, 2011 at 2:05 am
It's confusing...
One statement:
Elements can be added, deleted or even the sequence can be adjusted within the xml. Only the values of need to be persist.
Another statement:
value red apple and value goldstar. => so red apple needs to be replaced with goldstar
The first statement requires the values to be persistent and the structure to change. But the second changes the values but not the structure.
After clarifying the true requirement you might want to shred the xml into a relational table and export it back using the new structure.
Other than that, there's not really an easy concept to compare apples and oranges and make them all look like ananas...
Edit: another question: do you really use SQL Server 2000? (as indicated by the forum you posted in)
September 9, 2011 at 2:22 am
=> Changing the sequence is functionality of the application not of the implementation of the stored procedure. The format of the xml comes within the parameter @xml. The only thing is that I need the old values witin the records. That is it. (in a simply way. )
The difficult part is that i don't know the name of the elements and i do not know which elements exists in the records.
=> comparing apples and oranges: Probably trying to explain too much. I only want the old valuies of the old xml within my new xml. I was describing the problem in this way because i read a lot of articles which were explaining with a lot of xml statements to go through the elements to indeed shred the xml into a table or doing something else.
'2000' Oops didn't see that. Was reading a post what did discussed a bit in my direction and i have created this post from that point. No i'm using 2008 R2
September 9, 2011 at 5:45 am
So you're basically getting a new schema where you have to map existing data to?
If that's the case I would request the new schema instead of an unknown XML structure where I'd have to reverse-engineer the schema.
There are tools available to extract the schema based on a given structure. Maybe you could even use the related SSIS functionality.
Using XQuery to do it is close to a nightmare...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply