SQLXML - Parent Child Relationships

One of the data guys I work with is a big advocate of guaranteeing data integrity through identity columns and foreign keys. Granted, identity columns work great if you like numeric key values, you only work with one database and you don’t mind an extra round-trip to the database to retrieve the identity before you can insert it as a foreign key somewhere else. If you oppose to any of these items or if you prefer generating key values yourself instead of letting SQL Server do it, then you add the identity="useValue" annotation to your mapping schema and skip ahead. On the other hand, if you work with a data guy like I do and you have to deal with SQL Server generated identities you need to read this section.

Imagine you need to insert the data from the following XML document into a database where the OrderID column in the OrderHeader table is an IDENTITY column. The rows in the OrderLine table references their parent in the OrderHeader table by that OrderID column. Which of the insert techniques we discussed in section 15.3.2 would you choose?

<OrderHeader>

  <CustomerID>987</CustomerID>

  <RequiredDate>2002-12-25</RequiredDate>

  <OrderLine>

    <ProductID>12</ProductID>

    <UnitPrice>10</UnitPrice>

  </OrderLine>

</OrderHeader>

If you chose to implement your inserts by hand after you read sections 15.3.2.1 and 15.3.2.2 then you can skip right on to the Summary section because you do not have a problem – at least not with respect to inserting the foreign key values into the OrderLine table. You have to hand code all your SQL statements to insert your data, i.e. you can retrieve the IDENTITY after you insert the parent and propagate it to the children. If you chose to implement your inserts with DataSets and the SqlXmlAdapter then you did not pay attention when you read section 15.3.2.4. You cannot insert nested XML elements if you need to propagate a SQL Server generated IDENTITY to children if you are doing the insert via a DiffGram, which is what the SqlXmlAdapter/SqlXmlCommand combination does.

If you chose to do your inserts via Updategrams despite the warning, then I better give you a few pointers how you can accomplish this. Updategrams allow you to specify a placeholder to reference the automatically generated identity from the child elements. We define the placeholder with the at-identity attribute at the root of an XML type.

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">

  <updg:sync>

    <updg:after>

      <OrderHeader updg:at-identity="x">

        <OrderID>x</OrderID>

        <CustomerID>987</CustomerID>

        <RequiredDate>2002-12-25</RequiredDate>

        <OrderItem>

          <ProductID>1</ProductID>

          <Quantity>10</Quantity>

        </OrderItem>

      </OrderHeader>

    </updg:after>

  </updg:sync>

</ROOT>

With this restriction, writing a generic method to generate an Updategram as we did in 15.3.2.4 becomes harder. We can no longer insert the XML document as it is into an updategram. The at-identity attribute and the placeholders need to go into the XML document. To add them we can either read the XML document into an XmlDocument object to add the attribute and the identity references, or we can run the document through an XSLT. Either way we lose the major benefit we gained from using updategrams: easy to maintain code that was independent of the XML format our applications are dealing with. Furthermore the extra step parsing or transforming the document to add the identity placeholders requires extra processing cycles and demands more memory to build the complete updategram.

You have to decide for yourself if these issues around inserts are show stoppers adopting updategrams in your application. If you want to use them for updates and deletes you may as well stick with them for inserts. Or maybe your primary use case involves running XPath queries and you are already have to maintain a mapping schema, inserts happen rarely and you do not care so much about the performance hit. The best thing you can to do is to look at your XML schema and  see how hard it is in your case to add the placeholders. Maybe you run a few tests to see how badly the performance is impacted in your scenario. The book’s web site has one example on how to insert the at-identity attribute if you can assume that the relationship is realized with elements of the same name in parent and children. Maybe this code gets you going into finding a solution for your scenario.

Summary

We have seen that we can implement XML-based data access without moving to a native XML database. We can stick with SQL Server 2000 and keep all our existing applications from before the XML era. You can also keep all our third party reporting tools and whatever else you have. SQL Server 2000 and SQLXML offer some very interesting features that simplify developing XML-based data access without major impact to an application’s scalability. Especially when it comes to retrieving XML from SQL Server I cannot think of a good reason to not let SQLXML handle the XML formatting of the results.

Updategrams and Diffgrams to insert data and performing updates are not quite as much a hands-down winner as XPath   queries. They are still “held hostage” by the gritty details of the relational model and SQL, but bear in mind that with the next release of SQL Server, XML, as well as the .NET Framework, become completely integrated into the database.

Overall, XML based data access already simplifies the development of XML-enabled applications. Annotated schemas keep all XML to database mapping rules in one place and XPath queries are a concise and powerful alternative to complex statements JOINing data from multiple tables.

After all the hierarchical structure of XML maps more natural to the hierarchical object models we use in our applications. Combine data binding with the XmlSerializer and SQLXML for example and you have a simple Object/Relational mapper. You can load and persist objects to the database without developing custom code. The mappings are defined in an annotated XML schema, which we can use to generate C# or Visual Basic.NET classes. The XmlSerializer handles the object-to-XML conversion while SQLXML does the XML-database mapping. There are many, many more use cases for XML and XML-based database access. You know have the knowledge to pick the best solution for your particular scenario.