In some of the previous posts, we saw how to delete an element or attribute from an XML document (variable/column). In this post, let us examine one more example that deletes elements from an XML document matching a given criteria.
I wrote this example some time back, to help some one at one of the forums. There is a table with an XML column that contains XML documents like the one given below.
<Root>
<Feed id="2008-08-28T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
<Feed id="2008-09-30T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
</Root>
The task is to delete "feed" elements from the XML documents that is older than 30 days. In the above example, the first "feed" element is older than 30 days (today is 11 Oct 2008) and hence should be deleted after the operation.
Here is the sample script that performs this operation. It creates a memory table with an XML column and inserts two records. Then it performs a delete operation.
-- Create a memory table
DECLARE @t TABLE (data XML)
-- Populate the table with two records
-- Record 1
INSERT INTO @t (data) SELECT '
<Root>
<Feed id="2008-08-28T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
<Feed id="2008-09-30T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
</Root>'
-- Record 2
INSERT INTO @t (data) SELECT '
<Root>
<Feed id="2008-08-28T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
<Feed id="2008-09-30T09:49:34.780Z">
<Content>some content here</Content>
</Feed>
</Root>'
-- Take the date value to a variable for comparison
DECLARE @d AS NVARCHAR(30)
SET @d = CONVERT( NVARCHAR(30), GETDATE()-31, 126) + 'Z'
-- Perform the update
UPDATE @t SET
data.modify ('
delete
/Root/Feed[@id cast as xs:dateTime ? <
sql:variable("@d") cast as xs:dateTime ?]
')
-- Let us check the results
SELECT * FROM @t
/*
<Root><Feed id="2008-09-30T09:49:34.780Z"><Content>some content here</Content></Feed></Root>
<Root><Feed id="2008-09-30T09:49:34.780Z"><Content>some content here</Content></Feed></Root>
*/
Do you have an XQuery question? Feel free to write to me. I will try to help you out.
We have seen several XQuery examples in the previous posts in this series. We briefly discussed XML namespaces in a couple of posts earlier (XML Namespaces, SQL Server 2005 XML and Default Namespaces). Some times, reading values from an XML document having namespace declarations might seem little tricky. Let us look at a few examples having namespace declarations and see how to read information from the XML documents.
Here is the sample XML we will examine in this lab.
<Configuration
xmlns:db="urn:jacobsebastian.blogspot.com/databaseconnection"
xmlns:net="urn:jacobsebastian.blogspot.com/internetconnection">
<net:Connection>
<net:Provider>World Wide Internet Providers</net:Provider>
<net:Speed>512 KBPS</net:Speed>
</net:Connection>
<db:Connection>
<db:Provider>SQL Client Provider</db:Provider>
<db:Protocol>TCP/IP</db:Protocol>
<db:Authentication>Windows</db:Authentication>
</db:Connection>
</Configuration>
This XML document has two namespace declarations. However, it does not have a default namespace. Alternatively, we could make one of the namespaces as default namespace. That will give us 3 different representations of the same XML document. Let us see how to read information from the three different representations of the XML document.
Example 1:
This example reads information from the version of the XML document that does not have any default namespace.
DECLARE @x XML
SELECT @x = '
<Configuration
xmlns:db="urn:jacobsebastian.blogspot.com/databaseconnection"
xmlns:net="urn:jacobsebastian.blogspot.com/internetconnection">
<net:Connection>
<net:Provider>World Wide Internet Providers</net:Provider>
<net:Speed>512 KBPS</net:Speed>
</net:Connection>
<db:Connection>
<db:Provider>SQL Client Provider</db:Provider>
<db:Protocol>TCP/IP</db:Protocol>
<db:Authentication>Windows</db:Authentication>
</db:Connection>
</Configuration>'
;WITH XMLNAMESPACES(
'urn:jacobsebastian.blogspot.com/databaseconnection' AS db,
'urn:jacobsebastian.blogspot.com/internetconnection' AS net
)
SELECT
n.value('net:Provider[1]','VARCHAR(30)') AS NetProvider,
n.value('net:Speed[1]','VARCHAR(10)') AS NetSpeed,
d.value('db:Provider[1]','VARCHAR(20)') AS DBProvider,
d.value('db:Protocol[1]','VARCHAR(10)') AS DBProtocol
FROM @x.nodes('Configuration/net:Connection') x1(n)
CROSS APPLY @x.nodes('Configuration/db:Connection') x2(d)
/*
NetProvider NetSpeed DBProvider DBProtocol
------------------------------ ---------- -------------------- ----------
World Wide Internet Providers 512 KBPS SQL Client Provider TCP/IP
*/
Example 2
This version of the XML document specifies the first namespace as the default namespace. Elements belonging to the default namespace are not prefixed with a namespace prefix. Note that the first namespace is declared as DEFAULT in the WITH XMLNAMESPACES block.
DECLARE @x XML
SELECT @x = '
<Configuration
xmlns="urn:jacobsebastian.blogspot.com/databaseconnection"
xmlns:net="urn:jacobsebastian.blogspot.com/internetconnection">
<net:Connection>
<net:Provider>World Wide Internet Providers</net:Provider>
<net:Speed>512 KBPS</net:Speed>
</net:Connection>
<Connection>
<Provider>SQL Client Provider</Provider>
<Protocol>TCP/IP</Protocol>
<Authentication>Windows</Authentication>
</Connection>
</Configuration>'
;WITH XMLNAMESPACES(
DEFAULT 'urn:jacobsebastian.blogspot.com/databaseconnection',
'urn:jacobsebastian.blogspot.com/internetconnection' AS net
)
SELECT
n.value('net:Provider[1]','VARCHAR(30)') AS NetProvider,
n.value('net:Speed[1]','VARCHAR(10)') AS NetSpeed,
d.value('Provider[1]','VARCHAR(20)') AS DBProvider,
d.value('Protocol[1]','VARCHAR(10)') AS DBProtocol
FROM @x.nodes('Configuration/net:Connection') x1(n)
CROSS APPLY @x.nodes('Configuration/Connection') x2(d)
/*
NetProvider NetSpeed DBProvider DBProtocol
------------------------------ ---------- -------------------- ----------
World Wide Internet Providers 512 KBPS SQL Client Provider TCP/IP
*/
Example 3
This is the third variation of the XML document that specifies the second namespace as the default namespace. The following example shows how to read information from this version of the XML document.
DECLARE @x XML
SELECT @x = '
<Configuration
xmlns:db="urn:jacobsebastian.blogspot.com/databaseconnection"
xmlns="urn:jacobsebastian.blogspot.com/internetconnection">
<Connection>
<Provider>World Wide Internet Providers</Provider>
<Speed>512 KBPS</Speed>
</Connection>
<db:Connection>
<db:Provider>SQL Client Provider</db:Provider>
<db:Protocol>TCP/IP</db:Protocol>
<db:Authentication>Windows</db:Authentication>
</db:Connection>
</Configuration>'
;WITH XMLNAMESPACES(
'urn:jacobsebastian.blogspot.com/databaseconnection' AS db,
DEFAULT 'urn:jacobsebastian.blogspot.com/internetconnection'
)
SELECT
n.value('Provider[1]','VARCHAR(30)') AS NetProvider,
n.value('Speed[1]','VARCHAR(10)') AS NetSpeed,
d.value('db:Provider[1]','VARCHAR(20)') AS DBProvider,
d.value('db:Protocol[1]','VARCHAR(10)') AS DBProtocol
FROM @x.nodes('Configuration/Connection') x1(n)
CROSS APPLY @x.nodes('Configuration/db:Connection') x2(d)
/*
NetProvider NetSpeed DBProvider DBProtocol
------------------------------ ---------- -------------------- ----------
World Wide Internet Providers 512 KBPS SQL Client Provider TCP/IP
*/
Reading information from SQL Server Maintenance Plan XML document
It is Kyle Gerbrandt who inspired me to write this post. After reading the XQuery Labs, he mentioned that it does not contain enough articles to explain how to work with XML documents having namespace declarations. He is working on a DBA Database Automation System where he needs to read information from the XML documents that SQL Server internally uses to store information about Maintenance Plans. Here is a sample XML document he sent me. (I have edited the document and kept only the data needed for this example). Here is the XML document.
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE">
<DTS:Executable
DTS:ExecutableType="Microsoft...."
DTS:ThreadHint="0">
<DTS:ObjectData>
<SQLTask:SqlTaskData
xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"
SQLTask:TaskName="Shrink Database Task" >
</SQLTask:SqlTaskData>
</DTS:ObjectData>
</DTS:Executable>
</DTS:Executable>
</DTS:Executable>
The task is to read the value of the attribute: SQLTask:TaskName. This XML document is bit different from the XML documents we have seen previously. There are two different namespaces we need to handle in this example. The element that holds the attribute we need to read belongs to a different namespace than its parent node. Let us see how to read the value of the above attribute from this XML document.
DECLARE @xml XML
SELECT @xml = '
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE">
<DTS:Executable
DTS:ExecutableType="Microsoft...."
DTS:ThreadHint="0">
<DTS:ObjectData>
<SQLTask:SqlTaskData
xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"
SQLTask:TaskName="Shrink Database Task" >
</SQLTask:SqlTaskData>
</DTS:ObjectData>
</DTS:Executable>
</DTS:Executable>
</DTS:Executable>'
;WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' as DTS,
'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask
)
SELECT
x.value('(@SQLTask:TaskName)','varchar(50)') as TaskName
FROM
@xml.nodes('
/DTS:Executable/DTS:Executable/DTS:Executable/DTS:ObjectData/SQLTask:SqlTaskData'
) v(x)
/*
TaskName
--------------------------------------------------
Shrink Database Task
*/
We saw an example of UNPIVOT in the previous post. Let us see one more UNPIVOT example in this post. I wrote this query to help some one in one of the SQL Server forums.
Here is the source data:
/*
SquadID Date Area01 Area02 Area03 Area04 Area05 Weather
----------- ----------- ------ ------ ------ ------ ------ -------
123 2008-09-19 5.1 2.1 3 10.2 8.5 Dry
*/
Here is the expected result.
/*
AREA Point Date Weather
---------- ---------------------- ----------------------- ----------
Area01 5.1 2008-09-19 00:00:00.000 Dry
Area02 2.1 2008-09-19 00:00:00.000 Dry
Area03 3 2008-09-19 00:00:00.000 Dry
Area04 10.2 2008-09-19 00:00:00.000 Dry
Area05 8.5 2008-09-19 00:00:00.000 Dry
*/
Here is the query that performs the required UNPIVOT operation.
-- Create a memory Table
DECLARE @t TABLE (
SquadID INT,
Date DATETIME,
Area01 FLOAT,
Area02 FLOAT,
Area03 FLOAT,
Area04 FLOAT,
Area05 FLOAT,
Weather VARCHAR(10))
-- Populate with sample data
INSERT INTO @t(SquadID, Date, Area01, Area02, Area03, Area04, Area05,
Weather)
SELECT 123,'2008-09-19',5.1,2.1,3.0,10.2,8.5,'Dry'
-- UNPIVOT Query
SELECT Area, Point, Date, Weather FROM @t
UNPIVOT
(Point FOR Area IN
(Area01, Area02, Area03, Area04, Area05)
)AS up
/*
AREA Point Date Weather
---------- ---------------------- ----------------------- ----------
Area01 5.1 2008-09-19 00:00:00.000 Dry
Area02 2.1 2008-09-19 00:00:00.000 Dry
Area03 3 2008-09-19 00:00:00.000 Dry
Area04 10.2 2008-09-19 00:00:00.000 Dry
Area05 8.5 2008-09-19 00:00:00.000 Dry
*/
For SQL Server 2000 Users
The UNPIVOT operator is not available in SQL Server 2000. My friend Manivannan D Sekaran wrote a version of the above query that works in SQL Server 2000.
-- Create a memory Table
DECLARE @t TABLE (
SquadID INT,
Date DATETIME,
Area01 FLOAT,
Area02 FLOAT,
Area03 FLOAT,
Area04 FLOAT,
Area05 FLOAT,
Weather VARCHAR(10))
-- Populate with sample data
INSERT INTO @t(SquadID, Date, Area01, Area02, Area03, Area04, Area05, Weather)
SELECT 123,'2008-09-19',5.1,2.1,3.0,10.2,8.5,'Dry'
-- SQL Server 2000 Version
select
Area = 'Area' + num ,
Point = case num when 1 then [Area01]
when 2 then [Area02]
when 3 then [Area03]
when 4 then [Area04]
when 5 then [Area05] end,
Date,
Weather
from @t
cross join (
select '01' as num
union all select '02'
union all select '03'
union all select '04'
union all select '05'
) numbers
/*
Area Point Date Weather
------ ---------------------- ----------------------- ----------
Area01 5.1 2008-09-19 00:00:00.000 Dry
Area02 2.1 2008-09-19 00:00:00.000 Dry
Area03 3 2008-09-19 00:00:00.000 Dry
Area04 10.2 2008-09-19 00:00:00.000 Dry
Area05 8.5 2008-09-19 00:00:00.000 Dry
*/
Do you need help with a PIVOT/UNPIVOT Query? Feel free to contact me. I will try to help you out.
This post demonstrates how to find the differences between two dates in HH:MM:SS format.
Some one asked me this question in the MSDN forum this morning. I wrote a query to answer the question. As you all can figure out, there is nothing very complicated here.
The approach that I used is to retrieve the difference in seconds and converted it into HOUR and MINUTE and the reminder as seconds. Well, that is simple again. What might be interesting is the usage of the STR() function along with the REPLACE() function. I used them to add a leading zeroes to HOURS, MINUTES and SECONDS if they have only one digit.
DECLARE @t TABLE (d1 DATETIME, d2 DATETIME)
INSERT INTO @t (d1, d2) SELECT '9/29/2008 11:36:22', '9/29/2008 8:30:25'
INSERT INTO @t (d1, d2) SELECT '9/29/2008 11:12:22', '9/29/2008 8:57:13'
SELECT
d1,
d2,
REPLACE(
STR(DATEDIFF(second, d2, d1) / 3600, 2) + ':' +
STR((DATEDIFF(second, d2, d1) % 3600) / 60, 2) + ':' +
STR((DATEDIFF(second, d2, d1) % 3600) % 60, 2), ' ', '0'
) AS diff
FROM @t
/*
d1 d2 diff
----------------------- ----------------------- --------
2008-09-29 11:36:22.000 2008-09-29 08:30:25.000 03:05:57
2008-09-29 11:12:22.000 2008-09-29 08:57:13.000 02:15:09
*/