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 r...