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

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 database is automatical
Read more

Brent: Programmer. Gamer. Cheapskate. All around good guy.

So... not being one that can leave a sore spot alone, when Brent Brown sent me an email Brent his "preferred" blog link so that I could update the link I had created to Stubenville , I naturally couldn't just update my previous blog entry. I needed to take the opportunity to pour lemon juice, salt, and vinegar into the wound until it became a pulp of agonized flesh. It may be because that's the way I am, but it may also be because Brent really needs this kind of opposition in his life. After all, he has a wonderful wife and cute kids, but as far as I can see, nobody that really gives him the harassing that he needs to keep his life in balance. That's where I come in. I don't know what the big deal is... after all, I liked the Stubenville (not Stupid-ville, that's a whole other wound that needs opening from time to time) thread. However, since he isn't there any more, I suppose that it has a limited shelf life. So, if anyone actualy reads my blog, you
Read more

Building nice XML from SQL Server Tables

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example: Unfortunately, the example result is listed before the code. Join all the desired tables together Set the desired root element Set the path to an empty string to avoid an extra XML element at the root of each resulting row Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well) Keep all the selected columns together by XPATH name or you'll split your xml attributes See my next blog entry for my best way to shred the xml
Read more