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 :
460
SQLXML - Mapping .NET Schemas
The first of the SQLXML features we are looking at are mapping schemas,
because several other features depend on them. Mapping schemas are very handy
when we have to build an XML enabled application on top of an existing SQL
Server database. Imagine the following scenario: Your boss finally caught on
that comma-separated files are no longer state-of-the-art to transfer data and
XML is now the way to go. The next time a business partner requires a data
feed, your assignment is to develop an application to send data from your
existing relational data base to the business partner in an XML format. If you
are lucky you get to define the XML for the feed yourself. In that case you
remember SQL Server’s built-in support for XML queries and chose a format that
mirrors your database schema because you can create the feed with a simple FOR
XML AUTO select statement, as we have seen in 15.1.2.2. Then you write a
component to issue the query and within two days you announce to your boss that
your project is done.
Unfortunately, that scenario is hardly ever the happening. More than likely
you wind up having to send data in an XML format that you cannot create with a
simple FOR XML AUTO query. Now what are you going to do? In some simple cases
you might be able to retrieve the data with a FOR XML AUTO query and employ XSL
transformations to get the output from SQL Server into the XML format you need.
If your format is structured as a hierarchy of types then XSLT will get very
complex. You could resort to SQL queries with the FOR XML EXPLICIT clause
(15.1.2.3), but we’ve already seen how cumbersome it is to develop these
queries for XML types with more than a handful of elements. What you really
need in that situation is an automated way to map your database schema to an
XML schema – and that’s exactly what mapping schemas do; in an XML-oriented fashion
of course.
A mapping schema is an XSD schema with special annotations describing how
the XML data types map to tables and columns in the database. The annotations
are special XML elements and attributes to define which type XML maps to which
SQL Server table and whether a column maps to an element, an attribute or if it
does not map to anything. Figure 15.1 illustrates relationship of types in an
XSD schema and tables a SQL Server database and the role of the schema annotations.
Figure 15.1: The annotations in a mapping
schema define how an XML type maps to a SQL Server database.
SQLXML looks for these annotations when it analyzes an XSD schema for
override the default mapping. Table 15.6 lists all the annotations SQLXML 3.0
recognizes in a mapping schema. By default SQLXML maps each top-level element
in an XSD schema to a table with the same name. All attributes and immediate
child elements of top level elements map to columns with the same name as the
XML node. If element names in the schema do not match column names in the
database for example, we can add the field annotation to the element definition
in the schema. This annotation tells SQLXML which database column maps to this
XML element. Other annotations provide additional mapping rules map from SQL
Server to XML types. One class of annotations helps SQLXML to tune the way to
it accesses the data in SQL Server to the constraints on the database. The
relationship annotation for example identifies parent-child relationships
between tables. We will examine this particular annotation shortly.
1.6 Schema
Annotations defined in the urn:schemas-microsoft-com:mapping-schema namespace.
The annotations provide detailed information how an XML schema type maps to
tables in a SQL Server database. Annotated schemas allow XPath queries
returning XML data structures defined in the schema and allow insert, update
and delete operations in the database directly from schema valid documents.
XSD annotations
Description
sql:datatype
Maps an XSD type to a SQL Server type when type or format conversions are
required, for example the XML format dateTime is not compatible with SQL
Server’s datetime. We need to add the datatype=”datetime” annotation to the
schema for SQLXML perform a format conversion to insert XML datetime values.
sql:encode
SQL Server can return data from image type columns either as base64
encoded data embedded in the returned XML document or as an expression by
which the data can be queried (dbobject queries) over the SQL Server HTTP
interface. The encode annotation tells SQL Server which of the two to return.
Possible values are “url” for a dbobject expression or “default” for embedded
base64 encoded data. You cannot annotate elements annotated with
sql:use-cdata or on the ID, IDREF, IDREFS, NMTOKEN, or NMTOKENS attribute types
with sql:encode. Check the SQL Server Books Online documentation for more
details on dbobject queries.
sql:field
Maps an XML element or attribute to a database column. You can only map
leaf nodes to database columns. SQLXML does not support mixed content models
sql:guid
Specifies whether SQL Server will insert a GUID value from an Updategram
or automatically generate a new value when inserting a new row.
Possible values for the guid annotation are: “generate” and “useValue”.
sql:hide
Excludes the annotation element or attribute from the returned XML
fragment. However, the excluded node can be part of an XPath query.
Modifications to the excluded node with updategrams also succeed.
sql:identity
Identifies a table’s identity column. Possible values are: “ignore” and
“useValue”. When the annotation specifies “useValue” SQL Server will insert
the value from an updategram into the database. “ignore” causes SQL Server to
automatically generate an identity value. Check 15.3.4.4 for more details.
sql:inverse
Indicates that a parent-child relationship between the XML type is the
opposite of the relationship between the corresponding database tables. The
inverse annotation is only valid on a relationship annotation. You can find
more information on the relationship annotation in 15.2.1.2.
sql:is-constant
Indicates that the annotated XML element does not map to the database.
SQLXML will generate the element when executing XPath queries and ignore it
in Updategrams. You cannot add this annotation to attributes.
sql:key-fields
Identifies the column(s) that uniquely identify a row in a table. Leaving
this annotation out can cause an unexpected order of XML elements.
sql:limit-field
sql:limit-value
limit-field and limit value together restrict the results returned by
XPath queries to the rows where the column identified by limit-field has the
value specifid in limit value. SQLXML ignores these annotations for
updategrams.
sql:mapped
Indicates SQLXML to ignore the annotated schema item. The item is not
generated in returned XML elements and cannot be part of an XPath query.
sql:max-depth
Specifies the maximum depth of recursive relationships. SQLXML will not
return results beyond the maximum depth when executing XPath queries.
sql:overflow-field
Identifies a database column that receives all XML content from an
updategram that did not map to the database.
sql:prefix
Specifies a character sequence to prefix the value from the database. This
allows generating valid XML ID and IDREFS, since these have to be unique
across an entire XML document. SQLXML will append the prefix when it
generates XML documents and strips the sequence when it is present in an
Updategram.
sql:relation
Specifies the SQL Server table mapping to an element or attribute.
Attributes can map to a table to realize ID/IDREFs as m:n relationships in
the database.
sql:relationship
A relationship annotation describes a parent-child relationship between
two tables mapping to nested elements or ID/IDREF/IDREFS references in an XML
document. The attributes: parent, child, parent-key, and child-key identify
the database column(s) which realize the relationship between the tables. The
columns identified as the child key do not always have to be part of the XML
type related to the child table.
sql:use-cdata
SQLXML will always return the content of the annotated element wrapped in
a CDATA section when use-cdata is set to “1” or “true”. The annotation is
meaningless for updategrams since the underlying XML parser always handles
CDATA sections correctly. You cannot combine the use-cdata and encode
annotations on the same element.
If your application has to support an already existing XML schema then you
can simply add these annotations to that schema. If there is no schema
describing your XML data types you can very easily create one using the XML
Schema Designer in Visual Studio.NET. You need simply drag a database or a
table from a SQL Server instance in the Server Explorer window onto the schema
designer surface and watch the definitions for the data types appear in the
schema. Now you have an XML schema which you can tailor to the needs of your
application and add the annotations for SQLXML. Once we created an annotated
schema we can use it in several ways:
Issue XPath queries against the database, either directly from the
client application or from within a Template.
Insert, update and delete data from the database by sending
Updategrams containing the changes to execute.
Insert, update and delete data from the database by sending
DiffGrams containing the changes to execute.
TIP: The easiest way to test mapping schemas during development is to set up
an HTTP interface to your database as described in the SQLXML documentation and
issue XPath queries for every XML type in the schema directly from a web
browser. The browser will display the query results or the error messages
returned by SQLXML. Don’t forget to turn off the “Show friendly HTTP error
messages” option if you are using Internet Explorer. Once you validated your
schema by querying for each XML type in the schema, you can go on to build
Templates and Updategrams and build an application around them.
