Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Jun 07 2013

AWESOME sql script for monitor the progress of a db restore

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Found at stackoverflow here…
http://stackoverflow.com/questions/152447/is-there-a-sql-script-that-i-can-use-to-determine-the-progress-of-a-sql-server-b