Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Updating a xml column

Author  Topic 

rama.nelluru
Starting Member

37 Posts
Posted - 2011-04-28 : 11:48:41
Hi,

I have to update an xml column by adding an attribute
to the exixting one. For example
<aprover username="abc@live.com palce="" userbackup="some" on this i need to add attributes userid and backupid which looks like this
<aprover username="abc@live.com userid="123" palce="" userbackup="some" backupid="123". I have to get the userid and backupid from another table for that username and backup and update the xml in another table. I have to update like 1000 rows. I need help guys.


RAM

edit: moved to proper forum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-28 : 12:50:48
You can use the modify method of xquery. There are examples here: http://msdn.microsoft.com/en-us/library/ms175466.aspx

In your case, it would be something like this:
DECLARE @x xml;
SET @x = '<aprover username="abc@live.com" palce="" userbackup="some"/>'

set @x.modify('insert attribute userid {"123"} into (aprover)[1]');
SELECT @x;
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-04-28 : 13:06:51
thanks for the reply.
I need to loop each and every row of the xml column and check for the username attribute and get the userid of that username from other table and add it to the xml clumn.

and same for the backUP AND BACKUP ID.

like that I have 1000 rows to update the xml column.
<aprover username="abc@live.com userid="123" palce="" userbackup="some" backupid="123".
need to wroite stpred procedure.

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-28 : 13:17:42
Don't loop through the rows, you can do it in a set-based query - as in this example. Here I am modifying data in table #a using data in table #b.
CREATE TABLE #a (id INT, x XML);
CREATE TABLE #b (id INT, v VARCHAR(32));

INSERT INTO #a VALUES (1, '<aprover username="abc@live.com" palce="" userbackup="some"/>');
INSERT INTO #a VALUES (2, '<aprover username="uuu@live.com" palce="" userbackup="more"/>');
INSERT INTO #b VALUES (1, '123');
INSERT INTO #b VALUES (2, '888');

UPDATE a SET x.modify('insert attribute userid {sql:column("b.v")} into (aprover)[1]')
FROM #a a INNER JOIN #b b ON  a.id = b.id;

SELECT * FROM #a;

DROP TABLE #a;
DROP TABLE #b;
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-04-28 : 13:37:39
please bare me because i am new to xml. This is what i have to do

I have user table with 2 columns userid and username
workflow table has xml column which contains 1000 columns in each row
it contains more than one approver node. it should update all approver nodes with userid.
<aprover username="abc@live.com place="" userbackup="some" etc../> as xmlrow
i need to write a script which will update workflow table at a shot such way that

script should get the username from the approver node and get the userid of that username from user table and add that attribute to the xml.

for each row i need to update the <approver > node by adding userid.

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-28 : 14:19:57
You can certainly do it using a loop and so on. But it would be lot more effort, more error-prone, and slower.

Based on what I understand from your description, doing it should be just like the example that I have shown above, probably with minor changes. However, very hard for me to say without seeing the sample data and expected output. If you find that you are stuck trying to make the changes, can you do the following?

First, take a look at Brett's helpful blog here:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Second, post the following:

1. Sample data from the table that has the XML data. Post two rows of data, that's all that is required. If the XML is very large, you can remove some of the elements, but whatever is left out has to be representative and well-formed.

2. Sample data from the table that has the user id and user name that would correspond to the xml data.

3. How you want the output to look like.

Remember that someone who is looking at the post does not know anything about your business rules etc. All they have is the data you post and the required output.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-28 : 17:49:02
Is the data type of the column really XML data type? The example data you posted does not seem to be well-formed XML (or even valid XML). If it is stored as a varchar(max) or other character type columns, you cannot update it using xquery. In that case, the only thing you will be able to do is to use replace function.

If you do want to use replace function, if there are extra spaces in the data, you need to be careful about that. Assuming stray spaces are not an issue, you can do it like this:
update w set
	varcharXMLCol = 
		REPLACE
		(
			w.varcharXMLCol,
			'username='+u.username,
			'username='+u.username+ ' userid = "' + cast(u.userid as varchar(32)) +'"'
		
		)
	from
		WorkflowTable w
		inner join UserTable u on w.varcharXMLCol like '%'+u.username+'%'

You will need to do similar things for the backup id.

But if the data is indeed XML, it would be more reliable to do the update using xquery. After I get home from work, I am going to try to change the sample data to what I think it should be and see if I can do the update using xquery.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-28 : 21:42:34
I modified your sample data to what I think it should be. So here is my sample data
CREATE TABLE dbo.Workflow (request XML);
INSERT INTO Workflow VALUES
('
<Request>
  <WorkFlow>
	<Step sequence="1">
	  <Approver username="abc@abc.com" type="primary" time="180" backup="xyz@abc.com" status="none" />
	  <Approver username="xyz@abc.com" type="backup" timeOut="180" backUp="" backupid="0" status="none" />
	</Step>
  </WorkFlow>
</Request>
');
INSERT INTO Workflow VALUES
('
<Request>
  <WorkFlow>
	<Step sequence="1">
	  <Approver username="111@rrr.com" type="primary" time="180" backup="222@rrr.com" status="none" />
	  <Approver username="222@rrr.com" type="backup" timeOut="180" backUp="" backupid="0" status="none" />
	</Step>
  </WorkFlow>
</Request>
');

CREATE TABLE dbo.Users(id INT, username VARCHAR(32));
INSERT INTO Users VALUES (1234, 'abc@abc.com');
INSERT INTO Users VALUES (2345, 'xyz@abc.com');
INSERT INTO Users VALUES (1234, '111@rrr.com');
INSERT INTO Users VALUES (2345, '222@rrr.com');


And then this is the update to the XML column.
This does it in three steps; there may be easier ways, but can't think of any atm.

-- insert the primary user id
;WITH A AS 
(
   SELECT 
      c1.value('@username','varchar(32)') workflow_user,
      a.*
   FROM 
      dbo.Workflow a 
      CROSS APPLY a.request.nodes('/Request/WorkFlow/Step/Approver[@type="primary"]') T1(c1)
)   
UPDATE a SET
   request.modify
   (
      'insert attribute userid {sql:column("u1.id")} 
      into (/Request/WorkFlow/Step/Approver[@type="primary"])[1]'
   )
from
   A left join Users u1 on u1.username = a.workflow_user;
   
--insert the backup user id

WITH A AS 
(
   SELECT 
      c1.value('@username','varchar(32)') workflow_user,
      a.*
   FROM 
      dbo.Workflow a 
      CROSS APPLY a.request.nodes('/Request/WorkFlow/Step/Approver[@type="backup"]') T1(c1)
)   
UPDATE a SET
   request.modify
   (
      'insert attribute userid {sql:column("u1.id")} 
      into (/Request/WorkFlow/Step/Approver[@type="backup"])[1]'
   )
from
   A left join Users u1 on u1.username = a.workflow_user


--insert the primary users backup id

WITH A AS 
(
   SELECT 
      c1.value('@backup','varchar(32)') backup_user,
      a.*
   FROM 
      dbo.Workflow a 
      CROSS APPLY a.request.nodes('/Request/WorkFlow/Step/Approver[@type="primary"]') T1(c1)
)   
UPDATE a SET
   request.modify
   (
      'insert attribute backupid {sql:column("u1.id")} 
      into (/Request/WorkFlow/Step/Approver[@type="primary"])[1]'
   )
from
   A left join Users u1 on u1.username = a.backup_user
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-04-29 : 08:18:13
thank you. I will try this and touchbase with you.

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-29 : 08:49:16
Ok. Just test it before you do anything real, because I do not fully understand the business rules, so I may have done something incorrect.'

Also, is it by choice that you are storing the data in an XML column? Based on my limited understanding of your data, this seems like very nice relational data. So if you have control over it, a better (in my opinon, a MUCH better) option would be to shred the data and store it in relational tables.
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-05-04 : 00:01:25
I tried running the script, it works fine but it is updating only the first approver node

<Approver username="111@rrr.com" userid="1234" type="primary" time="180" backup="222@rrr.com" status="none" />

in all the rows but not the second and third approer nodes in all rows

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-04 : 21:47:13
What you observed is to be expected. You ran only the first update. That inserts only the userid for the primary Approver node. In the sample code that I posted earlier, I had 3 updates statements: one for the userid of the primary approver node, second for the backup id of the primary approver node and a third one for the userid of the backup approver node.

Copy the code that I had posted to a test database, run the statements to create and populate the tables. Then, run one update statement at a time and look at the data after each select statement. You will see what I mean.

Instead of doing it in 3 separate updates, one could perhaps combine them, but then the update script gets too complicated and error-prone, and will take a lot of effort to test and debug. This is much simpler and you can watch what happens after each update.
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-05-05 : 09:28:41
If you can send me your e-mail i will send the data ,

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-05 : 12:51:06
Sure, you can send e-mail from my profile in SQLTeam. Alternatively, you can post it here. Regardless, if you can e-mail/post the data and DDL for your tables, that would help - it seems like I may not be getting all your business requirements. Brett's post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx has info on how to post DDL etc.

Also, it may be worthwhile to take a closer look at the examples that I had posted to see what they are doing and modify them to your precise requirement. Pretty much everything I posted here is similar to examples on the MSDN page here: http://msdn.microsoft.com/en-us/library/ms175466.aspx. Now, I am not saying this to suggest that you should not ask for help here! Please do ask!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-05 : 16:35:57
I think I am confused now. If you need the results like this:
logonid or userid should be added <approver logonid="1234" logon="mnc@abc.com" ......>
<approver logonid="3456" logon="bcd@abc.com" ......>
<approver logonid="3456" logon="mnc@abc.com" ......>
It looks like logonId 3456 is shared by bcd@abc.com and mnc@abc.com. And, mnc@abc.com has two ids - 1234 and 3456. Also, even though in the input xml, you have abc@abc.com, it is not in the output. I think these may be typo's. Can you correct those and give me an accurate output for the input XML?

Also, please do ask questions - that is what the forum is for. I was pointing you to the documentation etc. so you can become fluent in writing and modifying these queries - which is not only useful, but also fun. I hope I didn't give you the impression that I
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-05 : 18:32:22
Hi,

Copy the script below to a test database and run it. The output it gives should be EXACTLY what you specified. If it is not, post the output and also the correct output you would like to see and describe the differences and we can fix it.

If it gives you the correct output, then you will need to change the table names and the column names to match your actual data. If you have difficulties doing that, post the DDL for your tables and I can help you with that. So here is the query:
--- CREATE TABLES WITH TEST DATA AND POPULATE--------------------------------------------
if object_id('tempdb..#tmpApproval') is not null drop table #tmpApproval;
create table #tmpApproval (approvalXML xml);
insert into #tmpApproval values
('<ApprovalRequest>
<ApprovalWorkFlow>
<ApprovalStep sequence="1">
<Approver logon="sunita.beck@abc.com" type="primary" userType="user" timeOut="180" 
backUp="justin.bieber@abc.com" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
<Approver logon="justin.bieber@abc.com" type="backup" userType="user" timeOut="180" 
backUp="" backupId="0" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
<Approver logon="miley.cyrus@abc.com" type="primary" userType="user" timeOut="180" 
backUp="" backupId="0" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
</ApprovalStep>
</ApprovalWorkFlow>
</ApprovalRequest>'
);

insert into #tmpApproval values
('<ApprovalRequest>
<ApprovalWorkFlow>
<ApprovalStep sequence="1">
<Approver logon="justin.bieber@abc.com" type="primary" userType="user" timeOut="180" 
backUp="sunita.beck@abc.com" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
<Approver logon="sunita.beck@abc.com" type="backup" userType="user" timeOut="180" 
backUp="" backupId="0" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
<Approver logon="miley.cyrus@abc.com" type="primary" userType="user" timeOut="180" 
backUp="" backupId="0" status="none" anonymous="false" auditor="false" 
notifyOnFailure="false" />
</ApprovalStep>
</ApprovalWorkFlow>
</ApprovalRequest>');

GO

if object_id('tempdb..#tmpUsers') is not null drop table #tmpUsers;
create table #tmpUsers (userid int, logonid varchar(32))
insert into #tmpUsers values (1001,'sunita.beck@abc.com')
insert into #tmpUsers values (1002,'zac.efron@abc.com')
insert into #tmpUsers values (1003,'justin.bieber@abc.com')
insert into #tmpUsers values (1004,'miley.cyrus@abc.com');

GO

--- Insert Approver Id's --------------------------------------------

declare @maxApprovalNodeCount int;
select @maxApprovalNodeCount = max(ApprovalXML.query('fn:count(//Approver)').value('.','int')) from #tmpApproval;

declare @ApproverNode int; set @ApproverNode = 1;
while (@ApproverNode <= @maxApprovalNodeCount)
begin
   ;WITH A AS 
   (
      SELECT 
         c.value('@logon','varchar(32)') logonid,
         a.*
      FROM 
         #tmpApproval a 
         CROSS APPLY a.approvalXML.nodes('(/ApprovalRequest/ApprovalWorkFlow/ApprovalStep/Approver)[sql:variable("@ApproverNode")]') T(c)
   )
   update A set
      approvalXML.modify
      (
        'insert attribute userid {sql:column("u.userid")} 
         into (/ApprovalRequest/ApprovalWorkFlow/ApprovalStep/Approver[sql:variable("@ApproverNode")])[1]'
      )
   from
      A
      inner join #tmpUsers u
         on u.logonid = a.logonid;
      
   set @ApproverNode = @ApproverNode + 1;

end;
GO

--- Insert Backup Id's --------------------------------------------
declare @maxApprovalNodeCount int;
select @maxApprovalNodeCount = max(ApprovalXML.query('fn:count(//Approver)').value('.','int')) from #tmpApproval;

declare @ApproverNode int; set @ApproverNode = 1;
while (@ApproverNode <= @maxApprovalNodeCount)
begin
   ;WITH A AS 
   (
      SELECT 
         c.value('@backUp','varchar(32)') backupid,
         a.*
      FROM 
         #tmpApproval a 
         CROSS APPLY a.approvalXML.nodes('(/ApprovalRequest/ApprovalWorkFlow/ApprovalStep/Approver)[sql:variable("@ApproverNode")]') T(c)
   )
   update A set
      approvalXML.modify
      (
        'insert attribute backupId {sql:column("u.userid")} 
         into (/ApprovalRequest/ApprovalWorkFlow/ApprovalStep/Approver[sql:variable("@ApproverNode")])[1]'
      )
   from
      A
      inner join #tmpUsers u
         on u.logonid = a.backupid;
      
   set @ApproverNode = @ApproverNode + 1;

end;

--- CLEAN UP
GO
select * from #tmpApproval;
if object_id('tempdb..#tmpApproval') is not null drop table #tmpApproval;
if object_id('tempdb..#tmpUsers') is not null drop table #tmpUsers;
GO
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-05 : 18:39:47
BTW, there is no way to specify the order of attributes in an element via the modify method. So when you look to see if there are all the attributes you wanted to add, don't expect to see them in the order that you had in the sample output.

The modify method of xquery in SQL XML is a Microsoft extension of the W3C xquery language. W3C does not have a modify method at all. So I guess MSFT was free to do it the way they wanted to. I would have liked to see an after/before/first/last clause for attributes, just like they have it for nodes. However, since the order does not make any difference in the case of attributes, the only use of it would be readability, so we can't complain too much about it.
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-05-05 : 19:53:20
i have thousand rows to update. can we write a stored procedure to update all the rows. At this point I cannot able to do anything.


RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-05 : 21:16:37
You don't necessarily have to have a stored proc - having a stored proc does not improve performance if you are going to do this just once or use it very infrequently. Regardless, we can make it into a stored proc, but before even attempting to that that the important question is: Did you get the results you expect from the query that I posted earlier this evening? If not, we need to fix that first.

If you would like me to help with making it into a stored proc, I have to see the table DDLs. Otherwise, I will be guessing the column names etc.
Go to Top of Page

rama.nelluru
Starting Member

37 Posts
Posted - 2011-05-06 : 07:47:54
it is workig for all the rows, i need to check for backup. Foe logon it is working perectly..

RAM
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-06 : 08:09:59
Whew! That is good!!

Check backup also, and if that is working correctly, and if you would like me to make it into a stored proc, post the table DDLs. Look up Brett's blog to see how to retrieve the table DDL. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site