update query using xml conversion

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

SQL Server 2008 Forums

Transact-SQL (2008)

update query using xml conversion

Author

Topic

danruziska
Starting Member

1 Post

Posted - 2012-01-03 : 12:20:22

Hello guys,

I have a XML query I'd like to solve:

There's a field within a table which is varchar, and contains a XML text. I need to extract a date and time fields (separate fields) from the XML and update a datetime field in another table. These are my drafts:

Here I convert from varchar to XML, so I can use XQuery to extract the date value (It's a XML tag with name=dtpDate and another subtag called Text, which contains the actual date)

declare @x nvarchar(MAX)
set @x = (select top 1 Convert(xml, [Xml]).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="dtpDate"]/Text)[1]', 'varchar(5000)')
from CS_CallLog
where Convert(xml, [Xml]).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="dtpDate"]/Text)[1]', 'varchar(5000)') is not null)

Tima value is within another xml tag, that's why I created another query:

declare @y nvarchar(max)
set @y = (select top 1 Convert(xml, [Xml]).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="txtTime"]/Text)[1]', 'varchar(5000)')
from CS_CallLog
where Convert(xml, [Xml]).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="txtTime"]/Text)[1]', 'varchar(5000)') is not null)

I convert both to datetime and concatenate them because they are separate fields:

declare @z datetime
set @z = (select CONVERT(datetime,@x,0))
select @z

declare @w datetime
set @w = (select CONVERT(datetime,@y,0))
select @w

select @z + @w

My goal is to get this resulting date and update a datetime field in another table.

The problem is I need to create this query to all the rows from CS_CallLog table (without the top clauses in the queries above), so I can't use a single variable. How can I create this query? Do I have to create a procedure?

Thanks a lot!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-03 : 12:49:04

Instead of using the variable, simply select from the table:

SELECT
	CAST([Xml] AS XML).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="dtpDate"]/Text)[1]','varchar(5000)') AS dtpDate,
	CAST([Xml] AS XML).value('(/DynamicFieldsAggregator/Controls/DynamicControls[Name="txtTime"]/Text)[1]', 'varchar(5000)') AS txtTime
FROM
	CS_CallLog

Now you can use this to join to another table and update, or whatever other operation you may need to do

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources