Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
275
SQLXML - Mapping Relationships To XML
It is very important that we specify all parent-child relationships between
the tables and designate key columns in a mapping schema. SQLXML needs this
information to be able to create the correct SQL statements when we issue XPath
queries and Updategrams. To map a parent-child relationship between XML types
to SQL Server tables we first have to add a relationship annotation to the
mapping schema. Then we add a reference to this relationship definition to the
element where the actual nesting occurs as depicted in the following diagram:
Figure 15.xx: The relationship annotation defines how nested XML types map
to database tables and which XML nodes map to primary and foreign key columns.
Note that SQLXML can also handle situations where parent-child relationships
between XML elements and SQL Server are opposites of each other. You can add
the inverse annotation to the relationship definition and SQLXML will swapping
parent and child when transforming XML to data in SQL Server.
Also, note that the key fields referenced in the relationship definition do
not need to be part of the XML type definition. SQLXML will automatically
generate the foreign key entry in the table identified as the child. Likewise,
SQLXML will appropriately generate entries for M:N relationships if they are
properly set up in the mapping schema.
