powershell
Get element max value from folder with XML files
Ever had a folder with a bunch of XML documents that you needed to query? Read on to see how you can extract values from those XML documents using PowerShell.
Microsoft SQL Server
Create XML with T-SQL function FOR XML
Some time ago I demonstrated how to use XQuery to map a hierarchical XML document into parent- child relational tables. You can find that article here. The other way around, (to create create XML with T-SQL), is a lot easier to do.
Mapforce 2014
Using MapForce to insert hierarchical XML to relational tables
In an earlier blog post, I demonstrated a way to insert a hierarchical XML document into a relational table structure by using XQuery. That solution works well in smaller solutions, but it’s a “code only” solution that can become complex and hard to maintain in larger projects.
An alternative solution would be to use the graphical mapping tool Mapforce from Altova.
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.