June 14, 2010 at 8:24 am
Hi All,
1. I have a xml Datatype column in my table named as CustomerData..
2. CustomerData can have 'n' number of nodes...
3. Each node contains an element called "ID" as GUID along with other elements Name, Address etc...
4. Now I will pass an ID and its CustomerData as INPUT..
5. If the ID is already exists(that means the Customer is already exists) then I want to replace the whole node with my INPUT Data
6. If not found the ID in any of the nodes then I want to insert the INPUT Data as a new node to the CustomerData
Could anyone please help me...Thanks in adavance
June 14, 2010 at 8:47 am
It's not very clear to me.
Try posting some sample data and the desired output.
You could also try reading the article linked in my signature line, it will help you rephrasing your post more effectively.
-- Gianluca Sartori
June 14, 2010 at 9:05 am
I have the below xml in my table, the user can add/edit the existing customer...so if I pass the whole customer data...I should see in the existing below xml...if found the customer(ID) the replace the whole customer info with the user input, if not found the need to insert the user input as a new node at end of the below..please let me know if it is still not clear...Thansk...
<customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>93DC8640-82D9-4078-84B8-5240C13567A3</ID>
<firstName>EVCARRIE</firstName>
<middleInitial />
<lastName>CONSUMER</lastName>
<address1>4437 Spruce Street</address1>
<address2 />
<city>Philadelphia</city>
<state>PA</state>
<zip>19104</zip>
<tin>123-45-6789</tin>
<birthDate>05/17/1954</birthDate>
<emailAddress>you@abc.com</emailAddress>
<phoneNumber1>270-554-4564</phoneNumber1>
<identificationType />
<licenseNumber />
<stateIssued />
<issueDate />
<expirationDate />
<employerName />
<employerPhone>333-333-3333</employerPhone>
</customer>
<customer>
<ID>F4D0ACF8-D47A-4F6A-ACEF-3EF3C8D25EC0</ID>
<firstName>EVCARRIE 2</firstName>
<middleInitial />
<lastName>CONSUMER 2</lastName>
<address1>4437 Spruce Street 2</address1>
<address2 />
<city>Philadelphia</city>
<state>PA</state>
<zip>19104</zip>
<tin>123-45-9876</tin>
<birthDate>05/17/1954</birthDate>
<emailAddress>you2@abc.com</emailAddress>
<phoneNumber1>222-222-2222</phoneNumber1>
<identificationType />
<licenseNumber />
<stateIssued />
<issueDate />
<expirationDate />
<employerName />
<employerPhone>270-554-5678</employerPhone>
</customer>
</customer>
June 14, 2010 at 2:03 pm
Usually, it helps a lot if you'd post something like
"here's what I have: ...
here's my desired result:...
those are the business rules to follow...
and here's what I've tried so far:...
"
For more details on how to provided sample data please see the first link in my signature.
Based on what I see so far I have no idea what you're looking for. Sorry.
June 14, 2010 at 2:53 pm
Here is What I have (DATA), 2 customers with ID, FirstName and LastName
customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>EVCARRIE</firstName>
<lastName>CONSUMER</lastName>
</customer>
<customer>
<ID>91BFB695-B100-494E-BB1E-8EA18A953726</ID>
<firstName>EVCARRIE 2</firstName>
<lastName>CONSUMER 2</lastName>
</customer>
</customer>
My INPUT is as below
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>Steve</firstName>
<lastName>Smith</lastName>
</customer>
In my INPUT the ID is '74374E76-DD16-473B-BC35-735E68234B5A' which is exist in DATA as first customer..so I need to update the DATA with my INPUT...i.e finally the OUTPUT should be as below
customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>Steve</firstName>
<lastName>Smith</lastName>
</customer>
<customer>
<ID>91BFB695-B100-494E-BB1E-8EA18A953726</ID>
<firstName>EVCARRIE 2</firstName>
<lastName>CONSUMER 2</lastName>
</customer>
</customer>
In which we can see the FirstName and LastName are updated with 'Steve' n 'Smith'
If my INPUT as below
<customer>
<ID>cebb7171-6019-4190-8b39-1c27f48996a0</ID>
<firstName>Jocab</firstName>
<lastName>Martin</lastName>
</customer>
In my INPUT the ID is 'cebb7171-6019-4190-8b39-1c27f48996a0' which is not exist in DATA..so I need to insert the DATA with my INPUT...i.e finally the OUTPUT should be as below
customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>Steve</firstName>
<lastName>Smith</lastName>
</customer>
<customer>
<ID>91BFB695-B100-494E-BB1E-8EA18A953726</ID>
<firstName>EVCARRIE 2</firstName>
<lastName>CONSUMER 2</lastName>
</customer>
<customer>
<ID>cebb7171-6019-4190-8b39-1c27f48996a0</ID>
<firstName>Jocab</firstName>
<lastName>Martin</lastName>
</customer>
</customer>
June 14, 2010 at 4:00 pm
Ok, what you're basically trying to do is an UPSERT (Update or Insert) based on existence of a customer ID. Right?
Why do you need to do it against XML files? Wouldn't it be a lot easier to store the XML data as a relational shredded structure? This would also be a better performing solution....
June 14, 2010 at 5:08 pm
Yup...You are correct...Thanks..
June 15, 2010 at 7:20 am
So, based on this, I've come up with:
-- set up and populate a "source" table
IF OBJECT_ID('tempdb..#OriginalData') IS NOT NULL DROP TABLE #OriginalData
CREATE TABLE #OriginalData (CustomerData XML)
insert into #OriginalData
values ('<customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>EVCARRIE</firstName>
<lastName>CONSUMER</lastName>
</customer>
<customer>
<ID>91BFB695-B100-494E-BB1E-8EA18A953726</ID>
<firstName>EVCARRIE 2</firstName>
<lastName>CONSUMER 2</lastName>
</customer>
</customer>')
-- set up and populate a "New Data" table.
-- if the ID is in the "source", update the remaining values.
-- if the ID is NOT in the "source", insert all values into the "source".
if OBJECT_ID('tempdb..#NewData') IS NOT NULL DROP TABLE #NewData
CREATE TABLE #NewData (CustomerData XML)
INSERT INTO #NewData
values ('<customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<ID>74374E76-DD16-473B-BC35-735E68234B5A</ID>
<firstName>Steve</firstName>
<lastName>Smith</lastName>
</customer>
<customer>
<ID>91BFB695-B100-494E-BB1E-8EA18A953726</ID>
<firstName>EVCARRIE 2</firstName>
<lastName>CONSUMER 2</lastName>
</customer>
<customer>
<ID>cebb7171-6019-4190-8b39-1c27f48996a0</ID>
<firstName>Jocab</firstName>
<lastName>Martin</lastName>
</customer>
</customer>')
;with OriginalXML AS
(
-- shred the original XML out into it's fields
select t.*,
ID = original.data.value('ID[1]','varchar(100)') ,
firstName = original.data.value('firstName[1]','varchar(100)') ,
lastName = original.data.value('lastName[1]','varchar(100)')
from #OriginalData t
CROSS APPLY t.CustomerData.nodes('/customer/customer') AS original(data)
), NewXML AS
(
-- shred the new XML (updates/inserts) out into it's fields
select NewCustData = t.CustomerData,
ID = new.data.value('ID[1]','varchar(100)') ,
firstName = new.data.value('firstName[1]','varchar(100)') ,
lastName = new.data.value('lastName[1]','varchar(100)')
from #NewData t
CROSS APPLY t.CustomerData.nodes('/customer/customer') AS new(data)
)
-- get the records to update
select *
from OriginalXML o
JOIN NewXML n
ON o.ID = n.ID
WHERE o.firstName <> n.firstName
OR o.lastName <> n.lastName
;with OriginalXML AS
(
-- shred the original XML out into it's fields
select t.*,
ID = original.data.value('ID[1]','varchar(100)') ,
firstName = original.data.value('firstName[1]','varchar(100)') ,
lastName = original.data.value('lastName[1]','varchar(100)')
from #OriginalData t
CROSS APPLY t.CustomerData.nodes('/customer/customer') AS original(data)
), NewXML AS
(
-- shred the new XML (updates/inserts) out into it's fields
select NewCustData = t.CustomerData,
ID = new.data.value('ID[1]','varchar(100)') ,
firstName = new.data.value('firstName[1]','varchar(100)') ,
lastName = new.data.value('lastName[1]','varchar(100)')
from #NewData t
CROSS APPLY t.CustomerData.nodes('/customer/customer') AS new(data)
)
-- get the records to insert
select *
from NewXML n
LEFT JOIN OriginalXML o
ON o.ID = n.ID
WHERE o.ID IS NULL
You'll want to look at these links for how to actually accomplish the update/insert into the XML data:
http://msdn.microsoft.com/en-us/library/ms187093.aspx
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx
http://whyiamright.wordpress.com/2008/01/02/updating-xml-column-in-sql-server-2005/
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply