Using XQuery to insert a XML hierarchy into parent / child tables

A XML document is hierarchical. A value gets meaning from its position in the document. Nodes can be nested so that a parent node can contain children nodes. This way the child nodes inherits all the values from the parent.

In a relational database the same thing can be achieved, but by using a different technology. Here we use key values in separate tables instead. Information from the parent or child can be retrieved by using relations between tables.

XML is commonly used in applications to store information about objects, and a relational database is often used to persist that information.

So how can a hierarchical XML document be inserted to relational tables? In this blog post I will show one possible technique, using only T-SQL and XQuery. In the example the tables will have their own internal primary keys defined which will add to the complexity, but it is a rather common scenario.

(more…)

Continue ReadingUsing XQuery to insert a XML hierarchy into parent / child tables

Zero footprint Server/Database DDL Trigger

Aaron Bertrand has written a great introduction to server/database DDL triggers in his article SQL Server DDL Triggers to Track All Database Changes.

I wanted to implement similar DDL triggers on more or less all servers. Some of the databases on those servers were from a 3:rd party, so changes in those databases wasn’t possible. Therefore I wanted to make sure these DDL triggers were “zero footprint”. That is, not causing existing database code to fail. I simply added TRY and an empty CATCH to the trigger, believing I’d be safe. Unfortunately, an error like this…

Msg 3616, Level 16, State 2, Procedure SP1, Line 5 An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
Msg 3616, Level 16, State 2, Procedure SP1, Line 5 An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

…happened to a SQL Agent job running during the evening.

(more…)

Continue ReadingZero footprint Server/Database DDL Trigger