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.