Unmatched Nodes in XQuery

By Michael Coles in Pro SQL Server XML | 02-25-2008 12:53 AM | Categories: Filed under: XML, new features, tutorial, programming, SQL Server, FLWOR, XQuery, function

Rating: (not yet rated) Rate this |

Discuss | 8,233 Reads | 703 Reads in Last 30 Days |no comments

In a previous post I talked about performing "inner joins" in XQuery. The basic idea is that the inner join is simply a special case of a Cartesian product, or "cross join". In this post I'm going to briefly look at another common SQL-style join condition that can be duplicated to some degree in XQuery. Specifically I'm going to give an example of a retrieving nodes from a tuple stream that don't match the nodes in a second tuple stream.

In this post we'll duplicate about 50% of the SQL "left outer join" functionality. For those who aren't familiar with SQL left outer joins, they can be thought of as an inner join between two tables unioned together with the rows of the left-hand table that have no corresponding rows in the right-hand table.

For this example I'm going to borrow some slightly modified XML data from the previous post:

DECLARE @xml xml; -- Create sample XML document SET @xml = N'<authors> <author id = "1">Fabio Claudio Ferracchiati</author> <author id = "2">Hugo Kornelis</author> <author id = "3">Rob Walters</author> <author id = "4">Lara Rubbelke</author> <author id = "5">Adam Machanic</author> <author id = "6">Michael Coles</author> <author id = "7">Robin Dewson</author> <author id = "8">Jan D. Narkiewicz</author> <author id = "9">Robert Rae</author> </authors> <books> <book title = "Pro T-SQL 2005 Programmer's Guide"> <isbn>159059794X</isbn> <author>6</author> </book> <book title = "Accelerated SQL Server 2008"> <isbn>1590599691</isbn> <author>3</author> <author>6</author> <author>7</author> <author>1</author> <author>8</author> <author>9</author> </book> <book title = "Pro SQL Server 2008 XML"> <isbn>1590599837</isbn> <author>6</author> </book> </books>'; -- Perform outer join SELECT @xml.query('for $author in /authors/author where fn:empty($author[@id = /books/book/author]) return <author> { $author } </author>');

This sample is available as a download file here.

The XML data consists of a list of authors and a list of books. The query uses a FLWOR expression to retrieve all authors who don't have a corresponding book node. Here's how it works:

The for clause binds the tuple stream /authors/author to the $author variable. By "binding the tuple stream", I mean that every author node is assigned to the $author variable in turn. For those from a C# background you can think of this as a foreach loop over the XML nodes specified by the path expression.
The where clause uses the fn:empty() function to determine if the id attribute of the current $author node exists in the /books/book/author node sequence. Basically we're checking to see if the current author's ID # is assigned to any given book or not. If not, the fn:empty() function returns true.
The return clause uses XML construction to build an <author> element for every author that meets the where clause condition (e.g., no matching book).

The results are shown below:

<author> <author id="2">Hugo Kornelis</author> </author> <author> <author id="4">Lara Rubbelke</author> </author> <author> <author id="5">Adam Machanic</author> </author>

As you can see, the three authors with id's of (2, 4, 5) were returned because they have no corresponding book nodes in the XML data. This technique, combined with the previously discussed inner join technique, can be used to simulate SQL style outer joins in XQuery.

Create a Dynamic Logging Trigger With XML

By Michael Coles in Pro SQL Server XML | 01-26-2008 8:47 PM | Categories: Filed under: XML, new features, programming, SQL Server, auditing, logging, trigger

Rating: |

Discuss | 6,548 Reads | 458 Reads in Last 30 Days |no comments

One of the more common uses of triggers is to create data manipulation language (DML) logging functionality. Essentially you can intercept and log inserts, updates, and deletes to tables. In the simplest case it's enough to know that one of these operations has occurred on a table, the date/time that it occurred, and some sort of identifying information for the user that performed the operation. At the other end of the requirements spectrum is the need to identify all information, including data indicating what exactly has changed.

What you usually find is that people who need to log DML operations will write one trigger and modify the same basic code for every other table that needs to be logged. I know one person who even wrote a client-side utility to automatically generate custom trigger code for hundreds of tables that he had to log. One problem with this method, apart from the sheer boredom of modifying the same trigger, again and again, is maintenance. If the underlying table structure changes your trigger is suddenly shot. In this post we're going to use FOR XML and SQL Server catalog views to create a dynamic trigger that will work on just about any table, and will automatically adjust it's output if the table structure changes.

To start with, we'll create a log table called dbo.AuditDml in the AdventureWorks database:

CREATE TABLE dbo.AuditDml (Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
SchemaName VARCHAR(100) NOT NULL,
TableName VARCHAR(100) NOT NULL,
TriggerName VARCHAR(100) NOT NULL,
LogTime DATETIME NOT NULL,
UserName VARCHAR(100) NULL,
SPID INT NOT NULL,
Changes XML);
GO

Next we’ll create a dynamic trigger. By dynamic I mean that you can run this script against any table and it will create a trigger that automatically detects its parent table and schema and logs all DML actions against the table properly, regardless of table structure. The example below creates the dynamic trigger on the AdventureWorks HumanResources.Shift table.

-- Change the schema and table name to match any
-- existing table in your database

CREATE TRIGGER HumanResources.AuditDml_Shift
ON HumanResources.Shift
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @Changes XML;
SELECT @Changes = COALESCE((
      SELECT *
    FROM deleted
    FOR XML AUTO), '') +
    COALESCE((
      SELECT *
      FROM inserted
      FOR XML AUTO), '')

  DECLARE @TriggerID INT;
DECLARE @TableID INT;
DECLARE @SchemaID INT;
DECLARE @SchemaName VARCHAR(100);

SET @TriggerID = @@PROCID;

SELECT @TableID = parent_id
FROM sys.triggers
WHERE object_id = @TriggerID;

SELECT @SchemaID = t.schema_id,
    @SchemaName = s.name
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE t.object_id = @TableID;

INSERT INTO dbo.AuditDml (SchemaName,
    TableName,
    TriggerName,
    LogTime,
    UserName,
    SPID,
    Changes)
SELECT @SchemaName,
    OBJECT_NAME(@TableID),
    OBJECT_NAME(@TriggerID),
    GETDATE(),
    USER_NAME(),
    @@SPID,
    @Changes;
END
GO

The dynamic trigger automatically determines it's schema and parent table name based on its own ID; so it doesn't matter what table this trigger is created on. It will always automatically detect this information with no special action on the part of the developer. The actual data changes are grabbed from the inserted and deleted virtual tables, which are dynamically structured using the FOR XML AUTO clause. FOR XML AUTO structures the XML data automatically based on the columns in the inserted and deleted virtual tables. Again, you don't have to do anything special, the trigger will automatically adjust its output based on the structure of the table - even if the table structure changes in the future. Now that we have a dynamic trigger created on the HumanResources.Shift table, we'll run a few DML statements to test it.

INSERT INTO HumanResources.Shift (Name, StartTime, EndTime)
VALUES ('Noon', '12:00:00', '20:00:00');

UPDATE HumanResources.Shift SET Name = 'Afternoon' WHERE Name = 'Noon';

DELETE FROM HumanResources.Shift WHERE ShiftId > 3;

These statements perform an insert, an update, and a subsequent delete of a new example shift I call the "Noon" or "Afternoon" shift. The actions are logged, as shown in the image below.

Each entry has information like the schema name, table name, date/time, user name, etc. The Changes column is an XML data type column with the contents of the inserted and deleted virtual tables in XML format. The image below shows the results of the sample UPDATE statement above.

xml changes log entry for update statement

This type of dynamic logging is especially useful when you have to log DML activity for several tables in a transactional system. A couple of caveats: This trigger may require some changes if your table contains LOB data type columns. Also always take care when using triggers on high-DML activity tables, as triggers of any kind can affect performance.

SQL Server Standard magazine will be publishing more of these tips for getting the most out of SQL Server XML in a future issue.

Welcome to Pro SQL Server XML

By Michael Coles in Pro SQL Server XML | 01-21-2008 5:38 PM | Categories: Filed under: XML, new features, methods, tutorial, introduction

Rating: (not yet rated) Rate this |

Discuss | 5,613 Reads | 421 Reads in Last 30 Days |1 comment(s)

Hi everyone, in this blog I plan to discuss the new SQL Server 2005 and SQL Server 2008 XML features. While I have several topics I plan to cover already, if you have specific SQL Server + XML related questions, feel free to contact me via this blog and I'll try to answer, or at least help you get a little closer to the answer.

I decided to kick this off with a simple example to demonstrate the utility of XML in SQL Server. SQL Server 2008 and 2005 store cached XML query plans that are accessible via the sys.dm_exec_query_plan dynamic management function. This function takes a query plan handle as an argument and returns the XML query plan, along with some other metadata.

While I was at the PASS Conference in Denver, I threw together a quick example to demonstrate shredding the cached XML query plans on SQL 2005. I expanded it a little bit to include some other information from the sys.dm_exec_sql_text dynamic management function and the sys.dm_exec_cached_plans dynamic management view.

WITH Plans(nodeid, physicalop, estimated_cost, plan_handle, text, query_plan, cacheobjtype, objtype) AS ( SELECT RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @NodeId', 'int'), RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @PhysicalOp', 'varchar(50)'), RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @EstimatedTotalSubtreeCost ', 'float'), cp.plan_handle, st.text, qp.query_plan, cp.cacheobjtype, cp.objtype FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //RelOp') RelOp (op) ) SELECT ROW_NUMBER() OVER (PARTITION BY p.plan_handle ORDER BY p.NodeId) AS Operation_Num, p.physicalop, p.text, p.cacheobjtype, p.objtype, p.estimated_cost FROM Plans p WHERE p.cacheobjtype = 'Compiled Plan';

Sample results of running this query on my local server are shown in the image below.

Sample xml query plan shredding

This sample query uses a few of the new features available starting with SQL 2005: the CROSS APPLY operator, Common Table Expressions (CTEs), dynamic management views/functions, and the ROW_NUMBER() windowing function.

The most important feature we're using here is the xml data type and its nodes() and value() methods. Since the XML query plans are stored as xml data type data, we are using the nodes() method to shred the XML data nodes into rows. Then we apply the value() method to each of these new rows to extract individual query plan operators and operator information from the plan. In this case we pull the physical operator name ("Nested Loops", "Filter", etc.) and the estimated subtree cost for each operator. As a bonus we are grabbing the initial SQL statement that is the basis for the plan from the sys.dm_exec_sql_text dynamic management view. That way we can easily relate the query plan, and its individual operators, back to the source SQL statement.

This is just one example of the capabilities of the xml data type. In upcoming posts I'll describe more precisely how the xml data type methods and XQuery work.