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)
StudentIdIdFirstNameLastSurname
21322C606-D644-497B-8548-207933921855ShelbyBarajas
219F5B105B-0B03-4508-B7B4-AD1731A85B6ESarahStevens
395757B9B9-C4AD-4B6D-B1A0-056B2D08B7BDGilbertFritz
58DE2FF0BA-F23B-4323-87C9-FA4AF3C3FA44DebbieSavage
76AFE73075-5460-4FBD-858B-E8864E8E1A38SandroMiguel
9547E92DBE-AE26-4F52-B532-5F8BD9E02826AliciaDennis
AddressIdStudentIdStreetNumberName
142654 Mission Hills
3221123 Simons Way
513916 Green Hague Way
6958123 Simons Way
887616 Green Hague Way
1079525 North Clarendon Boulevard
LanguageIdStudentIdLanguageDescriptorId
11495524
LanguageUseIdLanguageIdLanguageUseTypeId
1161141

Comments

Post a Comment

Popular posts from this blog

ATLAS AJAX Control Toolkit AutoCompleteExtender with Key Lookup

I like the ATLAS AJAX Control Toolkit. It integrates with Visual Studio and has useful if not basic javascript functions that can help a website be much more interactive. The AutoComplete Extender is used to provide auto-complete functionality to a text box. To use it, you simply add an extender and a web service method that provides a list of strings based on the text that the user started typing. So, if you are trying to provide the exact string that the user is typing (ala google search help), you can use it out of the box. However, sometimes you want more list-ish functions, that is, you want to present the user with a list of valid choices that represent objects, not just the descriptions. One alternative is to use a Listbox with a ListSearch Extender. This extender is used to find an existing value within the listBox. The downside to this approach is that every item you are looking for has to be in the list. This can be rather unwieldy if the list has more than a couple dozen i...
Read more

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your data...
Read more

Accessing a ServiceStack RESTful API using jQuery with CORS

The following applies to jQuery version 2.0.3 and ServiceStack version 3.9.59 My latest project is using ServiceStack.net  to create a REST-ful API for an existing data interchange format. There are a few gotchas that are important to understand: REST API DELETE Methods and jQuery The specification may be a bit vague, but most server frameworks (including ServiceStack) ignore any parameters passed to DELETE methods in the body. Only parameters passed on the URL are sent into the method. This behavior is similar to a HTTP GET. That is okay, but when you call an $.ajax method using jQuery, it doesn't serialize and attach the data to the URL, but instead puts it in the body...where the server promptly ignores it. In order to handle this apparent disconnect, and use the same semantics in $.ajax, you need to move the parameters from the body into the URL. The following ajax prefilter code does the trick: $.ajaxPrefilter(function (options, originalOptions, jqXHR) { if (options...
Read more