Transcript DAT405 Extreme SqlXml

DAT 405
Extreme SqlXml
Irwin Dolobowsky
Program Manager – SqlXml
Microsoft Corporation
Agenda
Where We’ve Been and Where We Are
Xml Views – The Core of SqlXml
Demo – XPath over Annotated Schema
Scenario – Data Exchange
Demo
XPath over Annotated Schema
Xml Bulkload
Scenario – Decoupled Business Objects
Demo
XPath over Annotated Schema
Templates
Updategrams
Other Scenarios
Discussion
Where We’ve Been
Started in 1998 to allow storage and retrieval
of XML data to and from SQL Server
Version 1.0 of product shipped as part of SQL
Server 2000
FOR XML and OpenXML in the Server
Templates, ISAPI, Annotated XDR for the Client
ADO/OLEDB Provider
New “Web Releases” of client functionality
are available via the MSDN web site:
http://www.msdn.com/sqlxml
Three fully supported releases since the initial
SQL Server 2000 release
Where We Are
Since Sql Server 2000
Updategrams
Bulkload
Annotated XSD (instead of XDR)
SqlXml Managed Classes
Client-Side FOR XML
Web Services Support
Current release – SqlXml 3.0 SP2
ID Propagation Support for Bulkload
NULL Support for Web Services
SqlXml - Architecture
SQL
Server
XSLT
XPath
Queries
Relation
tables
Updategrams
XML
Bulkload
Annotated
XSD
Mapping
Schemas
For XML
Queries
Relation
views
Relatio
Stored
n
procs
Xml Views
Virtual Xml Document over a SQL
Server database
Uses XSD schemas with
special annotations
Annotations specify the mapping between
xml and relational data
Query View using XPath subset
FOR XML EXPLICIT queries generated
Update through view with
Updategrams and Bulkload
Simple Mapping
<xsd:schema xmlns:xsd=“http://www.w3.org/2001/XMLSchema”
xmlns:sql="urn:schemas-microsoft-com:mapping-schema“>
<xsd:element name=“Customers”
sql:relation=“tblCustomers” >
<xsd:complexType>
<xsd:attribute name=“ID” sql:field=“pkCustID” />
</xsd:complexType>
Relational Data:
</xsd:element >
tblCustomers Table
</xsd:schema>
<Customers ID=“A”/>
<Customers/>
<Customers ID=“B”/>
pkCustID
-----A
NULL
B
Mapping With Hierarchy
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustOrders"
parent="Customers"
parent-key="CustomerID"
child="Orders" child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Customer" sql:relation="Customers" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name=“name” sql:field="companyName" />
<xsd:element name="Order" sql:relation="Orders"
sql:relationship="CustOrders">
<xsd:complexType>
<xsd:attribute name=“oid" sql:field="OrderID” />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="cid" sql:field="CustomerID" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
Storing Open Content
<xsd:schema xmlns:xsd=“http://www.w3.org/2001/XMLSchema”
xmlns:sql=“urn:schemas-microsoft-com:mapping-schema”>
<xsd:element name=“Customers”
sql:relation=“tblCustomers” sql:overflow-field=“Notes”>
<xsd:complexType>
<xsd:attribute name=“ID” sql:field=“pkCustID” />
</xsd:complexType>
</xsd:element >
</xsd:schema>
<Customers ID=“A”/>
<Customers ID=“B”/>
<Customers ID=“C” x=“4”>
<Name>Bob</Name>
</Customers>
pkCustID Notes
A
NULL
B
NULL
<Customers
C
x=“4”>
<Name>Bob</Name>
</Customers>
Querying Xml Views
Irwin Dolobowsky
Program Manager
SqlXml
Other Annotations
Sql:is-constant
Create an unmapped constant
element (wrapper)
Sql:hide
Query element/attribute with out surfacing
Sql:mapped
Element/attribute is not there
Advantages Of The Xml View
Easier to develop and maintain than
For XML Explicit queries
Multiple databases (with different
schemas) can provide a common
XML view
Update back through View
Decouple Shaping from Query
SqlXml Application Scenario
Data Exchange
Problem
Need to Exchange Data
between Companies
Databases schemas are different
Neither company can change database
SqlXml Application Scenario
Abstracting the Data Source
Solution – SqlXml!
Agree on schema for data to exchange
Produce XML from Server 1
Map database to the schema
Query mapping with XPath
Persist changes to Server 2
Same schema, different mapping
Use Bulkload to load the data
Works in reverse too
Data Exchange
Xml Bulkload
Irwin Dolobowsky
Program Manager
SqlXml
Xml Bulkload
Bulkload XML documents into a SQL
Server database via the XML View
Supports hierarchical relationships
and semi-structured data
Performance is about 75% of
traditional bulkload
New in SP2! – Identity Propagation
Values generated in Parent pushed to
child before inserting
SqlXml Application Scenario
Decoupled Business Objects
Problem
Mid tier performs business logic
over objects
Generating objects can be a hassle
Objects are hierarchical, not relational
Database limitations
Database schema is set
Want to batch updates to the database
SqlXml Application Scenario
Decoupled Business Objects
Solution – SqlXml!
Use XML Serialization
Produce XML from the database with XPath and
Annotated Schema
Use XML Serialization to produce objects from XML
Persist changes to SQL Server
Keep original XML Image
Serialize Objects back to XML when done
Produce an Updategram with the difference, send
to database
Use Overflow to store unconsumed data
Decoupled Business Objects
Object Serialization with SqlXml
Irwin Dolobowsky
Program Manager
SqlXml
Xml Templates
Xml Document with embedded queries
FOR XML T-SQL queries
XPath over Annotated Schema
Client-Side FOR XML
Execute Non-FOR XML Stored Procs
Raw, Nested (Auto*), Explicit Modes
Parameterized for re-usability
Post Process with XSLT
Updategrams
Modify the database through XML View
Easily represent hierarchical changes
Declare before and after images of
the XML
Optimistic concurrency control
Each updategrams generates one or
more INSERT, UPDATE, and
DELETE statements
Each sync block carried out as a
single transaction
Other SqlXml Scenarios
Data Driven Website
XPath and Xml Views to retrieve data
XmlTemplates/XSLT to generate pages
through ISAPI
Data Storage Abstraction
App works on top of XML Data
Underlying storage doesn’t matter
Save as files offline, to database online
More Information
SqlXml on MSDN
http://msdn.microsoft.com/sqlxml
http://msdn.microsoft.com/xml
Newsgroups
microsoft.public.sqlserver.xml
microsoft.public.dotnet.xml
My Blog
http://blogs.msdn.com/irwando
Community
http://www.SqlXml.org
Attend a free chat or web cast
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
List of newsgroups
http://communities2.microsoft.com/
communities/newsgroups/en-us/default.aspx
MIcrosoft Community Sites
http://www.microsoft.com/communities/default.mspx
Locate Local User Groups
http://www.microsoft.com/communities/usergroups/default.mspx
Community sites
http://www.microsoft.com/communities/related/default.mspx
Please fill out a session evaluation on CommNet
Q1: Overall satisfaction with the session
Q2: Usefulness of the information
Q3: Presenter’s knowledge of the subject
Q4: Presenter’s presentation skills
Q5: Effectiveness of the presentation
© 2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.