Shredding XML to Tables While Providing Parent-Child Keys

In my last post, I demonstrated how to shape and bend XML from your existing tables in SQL Server. In this post I am going to go the other direction and shred the xml directly into SQL tables with a desired structure. I will also create a parent-child relationship between entities where no keys previously existed in the xml.

This approach uses the older OPENXML mechanisms and is distinct from the value() and node() methods introduced later. I believe this approach to be superior and easier to construct than that demonstrated by Adam Machanic.

  • I'm assuming here that you've loaded the xml into the @xmlDocument variable (as in my previous example)
  • You need a document handle (@XmlDocumentHandle). As long as this handle remains open, the id's returned for each node do not change.
  • Use the OPENXML function with the @XmlDocumentHandle and create an XPath query that finds the data for the you want.
  • The value of 9 means to map attributes to your defined table, and handles really big documents.
  • The table definition is in the WITH clause
  • The @mp:id and @mp:parent are the identifier for the individual node and its parent. Every node in the document has a unique value that doesn't change; these are used to make our parent-child relationships work.
Here is some sample XML and SQL:
And here are some results: (sorry for the ugly tables)
142654 Mission Hills
3221123 Simons Way
513916 Green Hague Way
6958123 Simons Way
887616 Green Hague Way
1079525 North Clarendon Boulevard


Popular posts from this blog

KnockoutJS, WebAPI, and TypeScript

Database Projects, SQL Unit Tests, and TeamCity