Update complex XML - SQL 2008 R2

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 complex XML - SQL 2008 R2

Author

Topic

dmeenan
Starting Member

5 Posts

Posted - 2011-12-29 : 09:59:59

I have a boatload of COTS sql code stored in tables as XML (the whole beefalo). I need to alter some of the code but am running into problems with the complex XML. So I created a stripped down test of what I'm facing (shown below).

I want to alter this part of the <whereClause>: "dateadd(minute, -visitDtTm_offset, visitDtTm_dttm)"
to be this: "dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm)"
AND
I want to alter this part of the <whereClause>: "dateadd(minute, -schedDate_offset, Date_dttm)"
to be this: "dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm)"
WHERE queryid = 1

I can't get anywhere - feel rather dimwitted. Spent hours on this last night. Here is the test code:

set nocount on
if object_id('xmltest') is not null drop table xmltest
go
create table xmltest (queryid int, query xml)
go
insert into xmltest (queryid, query) values(1, '<query xmlns="queryproc.xsd">
<select><selectList><column name="*" type="text"/></selectList><fromClause><table name="[TESTDB].[dbo].[v_TESTVIEW]"/></fromClause><whereClause><condition value="dateadd(minute, -visitDtTm_offset, visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -schedDate_offset, Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -schedDate_offset, Date_dttm) DESC)"/></whereClause><orderByClause><column name="dateadd(minute, -visitDtTm_offset, visitDtTm_dttm)" order="ASC"/><column name="category" order="ASC"/></orderByClause></select></query>')
go
insert into xmltest (queryid, query) values(2, '<query xmlns="queryproc.xsd">
<select><selectList><column name="*" type="text"/></selectList><fromClause><table name="[TESTDB].[dbo].[v_TESTVIEW]"/></fromClause><whereClause><condition value="dateadd(minute, -visitDtTm_offset, visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -schedDate_offset, Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -schedDate_offset, Date_dttm) DESC)"/></whereClause><orderByClause><column name="dateadd(minute, -visitDtTm_offset, visitDtTm_dttm)" order="ASC"/><column name="category" order="ASC"/></orderByClause></select></query>')
GO
--SELECT * FROM XMLTEST
go
--can I detect any nodes? Nope.

DECLARE @i int, @nodeCount int
SET @i = 1
SELECT @nodeCount = query.value('count(/select/whereClause/condition/@value)','int') FROM xmltest
PRINT 'Number of nodes found: ' + cast(@nodeCount as varchar(2))

/*
--Tried this, and many other variations and FAILED!
declare @newValue varchar(1000)
set @newValue = 'condition value="dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) DESC)"'

update xmltest
set query.modify('replace value of (/query/select/whereClause) with sql:variable("@newValue")')
where queryid = 1
*/

Any and all help is appreciated.
And I’ll name my next kid after you.
/unless your name is Engelbert Humperdinck

Dan Meenan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 10:50:08

Not sure what other issues there may be, but your namespace is "queryproc.xsd", so you need to specify that (or declare that as the default namespace).

Also, select is not the root node (query is), so you need to either use //select or /query/select/...

For the count query that you tried, it should then be something like this:

DECLARE @i int, @nodeCount int
SET @i = 1
SELECT @nodeCount = query.value('declare namespace ns="queryproc.xsd"; count(//ns:select/ns:whereClause/ns:condition/@value)','int') FROM xmltest
PRINT 'Number of nodes found: ' + cast(@nodeCount as varchar(2))

dmeenan
Starting Member

5 Posts

Posted - 2011-12-29 : 12:48:42

Thanks for responding. Following your lead, I read up on declaring the namespace; I'm not up on w3c :-(
So then I tried this:

declare @newValue varchar(1000)
set @newValue = 'condition value="dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) DESC)"'
update xmltest
set query.modify('declare namespace ns="queryproc.xsd"; replace value of (/ns:query/ns:select/ns:whereClause/ns:condition) with sql:variable("@newValue")')
where queryid = 1

No Joy! I'm going to keep hacking/reading. Don't really expect you guys to spoon feed me (got some pride, although it's getting smaller) Got this error:

Msg 2337, Level 16, State 1, Line 4
XQuery [xmltest.query.modify()]: The target of 'replace' must be at most one node, found 'element(ns{queryproc.xsd}:condition,xdt:untyped) *'

AbelianCommuter

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 13:08:47

Two things about your query:

First, I think you want to modify the value attribute, not the node itself, if I am not mistaken.

Second, the limitation with XML modify is that in one xml document/fragment, you can modify only one value at a time. So you have to change the query to explicitly tell SQL that you are modifying one value. It is very picky in that, it is not sufficient if the query evaluates and results in a single node/attribute, but even before evaluation, it has to be clear that it would result in only one node/attribute.

So if you change it to this, it should stop complaining (See changes in red).

declare @newValue varchar(1000)
set @newValue = 'condition value="dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) DESC)"' 
update xmltest 
set query.modify('declare namespace ns="queryproc.xsd"; replace value of (/ns:query/ns:select/ns:whereClause/ns:condition/@value)[1] with sql:variable("@newValue")') 
where queryid = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 13:18:01

Following up on my previous post, if you are trying to change the data in every row in the table, it may be better to avoid the intermediate variable altogether and do it all at once. The following code compiles and updates something, but I don't know enough about your problem to say whether it is updating the correct rows.

;WITH XMLNAMESPACES (DEFAULT 'queryproc.xsd' ),
cte AS
(
   SELECT
      x.*,
      REPLACE
      ( 
         c.value('@value','varchar(max)'),
         'dateadd(minute, -visitDtTm_offset, visitDtTm_dttm)',
         'dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm)'
      )  AS newWhereClause
   FROM
      xmltest x
      CROSS APPLY x.[query].nodes('//whereClause/condition') T(c)
)
UPDATE cte SET
   [query].modify('replace value of (//whereClause/condition/@value)[1] with sql:column("newWhereClause")');

In the cte I am taking the current where clause and modifying it so it will be available for the update in the outer query.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 14:12:46

Did I mention I hate XML

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dmeenan
Starting Member

5 Posts

Posted - 2011-12-29 : 14:15:05

Sunita,

Thank you! Thanks you! As I can tell from your blog, you are one sharp cookie. The code shown below was the update statement that did the trick (minor tweak to @newValue). I need to do targeted updates so your CTE solution I will keep in abeyance for that inevitable future where it becomes crucial. I don't know if/how I mark this as resolved? Moderators?

Gratefully yours,

Dan

declare @newValue varchar(1000)
set @newValue = 'dateadd(minute, -isnull(visitDtTm_offset,-240), visitDtTm_dttm) in (SELECT DISTINCT TOP 1 dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) FROM [Visits].[dbo].[Dental] ORDER BY dateadd(minute, -isnull(schedDate_offset,-240), Date_dttm) DESC)'
update xmltest
set query.modify('declare namespace ns="queryproc.xsd"; replace value of (/ns:query/ns:select/ns:whereClause/ns:condition/@value)[1] with sql:variable("@newValue")')
where queryid = 1

AbelianCommuter

dmeenan
Starting Member

5 Posts

Posted - 2011-12-29 : 14:19:07

Oh and thanks to Brett too! I will follow the instructions in the links you provided going forward. Although I've been a C#/DBA for > 10 years, I've been shielded from the Boschian Hellscape that is w3c complex XML - not feeling the love.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 14:43:45

aw.. you are very welcome.

SQLTeam does not have the practice of marking threads as resolved/answered, so let it be.

Now, about that naming rights that you promised in your original posting... it is spelled S-U-N-I-T-A. I am just saying. No pressure, you know...

dmeenan
Starting Member

5 Posts

Posted - 2011-12-29 : 15:02:50

I'll let my better half know tonight. My 8-y-o daughter always wanted a little sister, but had to settle for a little brother.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources