Dev 399 SQLXML XML Views and Relational Data

Dev 399 SQLXML: XML Views and Relational Data Irwin Dolobowsky Program Manager Webdata Group Microsoft Corporation

Overview of talk Goals of presentation: Learn about the XML View Examine leveraging Xml View in application scenarios Agenda: A survey of the Sql. Xml features that provide the XML View Discuss strengths and weaknesses of each feature See the features in action

Sql. Xml – A Quick History The project started in 1998 to solve XML to relational mapping problem Version 1. 0 of product shipped as part of SQL Server 2000 Both Client and Server functionality 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.

Different kinds of data Structured Highly regular, homogeneous structure Rowsets, Comma delimited files Semi-Structured Heterogeneous structure Sparse Occurrences of data HTML and XML documents Unstructured Documents/Content

The Two Worlds Language SQL Data storage SQL Server Data output Row. Set Structured world XPath XML View SQLXML – Bringing worlds together XML Files XML/ HTML Semi. Structured world

What is the “XML View” ? The XML view is a virtual collection of XML documents created over a SQL Server database The XML view can completely encapsulate and hide the underlying relational structures The resulting XML documents can easily represent hierarchical, semi-structured content

The XML View - Architecture SQL Server XSLT XPath Queries Relation tables Updategrams XML Bulkload Annotated XSD Mapping Schemas For XML Queries Relation views Relation Stored procs

Annotated Mapping Schemas Defines an XML View of a database Uses XSD schemas with special annotations Annotations specify the mapping between xml and relational data XPath is used to query the XML View Changes to an XML View are persisted to database via Updategrams

Tables and Columns Default mapping: Elements map to tables of the same name Attributes map to columns in the table Explicit mapping: sql: relation maps to a table sql: field maps to a column

Default Mapping <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema“> <xsd: element name=“Customers”> <xsd: complex. Type> <xsd: attribute name=“Cust. ID”/> </xsd: complex. Type> </xsd: element > </xsd: schema> Relational Data: Customers Table XML View <Customers Cust. ID=“A”/> <Customers Cust. ID=“B”/> Cust. ID -----A NULL B

Explicit Mapping <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema“ xmlns: sql="urn: schemas-microsoft-com: mapping-schema“> <xsd: element name=“Customers” sql: relation=“tbl. Customers“ > <xsd: complex. Type> <xsd: attribute name=“ID” sql: field=“pk. Cust. ID“ /> </xsd: complex. Type> </xsd: element > </xsd: schema> Relational Data: tbl. Customers Table <Customers ID=“A”/> <Customers ID=“B”/> pk. Cust. ID -----A NULL B

Join Relationships Require an explicit annotation to describe the join: < sql: relationship parent=“Table 1" parent-key=“pk" child=“Table 2” child-key=“fk" /> Expresses the SQL join SELECT * FROM Table 1 JOIN Table 2 ON Table 1. pk= Table 2. fk

Join Example <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: sql="urn: schemas-microsoft-com: mapping-schema"> <xsd: element name="Customer" sql: relation="Customers“ > <xsd: complex. Type> <xsd: sequence> <xsd: element name="Order" sql: relation="Orders“ > <xsd: annotation> <xsd: appinfo> <sql: relationship parent="Customers" parent-key="Customer. ID" child="Orders" child-key="Customer. ID" /> </xsd: appinfo> </xsd: annotation> <xsd: complex. Type> <xsd: attribute name="Order. Date" type="xsd: date. Time"/> </xsd: complex. Type> </xsd: element> </xsd: sequence> <xsd: attribute name="Customer. ID" /> </xsd: complex. Type> </xsd: element> </xsd: schema>

Some other annotations Ignore schema components: <xsd: attribute name=“Not. In. The. DB” sql: mapped=“ 0” /> Query on field which is not returned in results: <xsd: attribute name=“Hidden. ID” sql: hide=“ 1” /> Introduce XML elements that do not map to the database: <xsd: element name=“Order. List” sql: is-constant=“ 1” > <xsd: complex. Type> … </xsd: complex. Type> </xsd: element>

Can even map Open Content Map SQL data as XML text: <xsd: element name=“E” sql: overflow-field=“Notes” /> <E> <Overflow. Stuff> <A b=“c”/> </Overflow. Stuff> </E> Notes -----<Overflow. Stuff> <A b=“c”/> </Overflow. Stuff>

XPath Navigation language (used by XSLT to select nodes in an XML document) Compact syntax resembles file paths or URLs: /Customer/Order[@Order. ID=‘ 10692’] Each XPath translates into a SQL query using FOR XML EXPLICIT to construct the XML For complete details on XPath, see the W 3 C specification at: http: //www. w 3. org/TR/XPath

demo XPath and XML Views Irwin Dolobowsky Program Manager Webdata

XPath and the XML View in real life The shape of the generated XML instance document can be very different then the underlying relation storage Multiple databases (with different schemas) can provide a common XML view Much easier to develop and maintain than For XML Explicit queries

For XML – Explicit Mode Provides complete control over format of XML result Columns can be individually mapped to attributes or sub elements Supports arbitrary nesting Collapses/hoists hierarchy Constructs ID/IDREF relationships Explicit mode requires the SELECT query to be written in a certain way to produce the “universal table format”

For XML – Explicit Mode SELECT 1 as TAG, NULL as PARENT, Customer. ID AS [Customer!1!cid!id], Company. Name AS [Customer!1!name!element], NULL AS [Order!2!oid!id] FROM Customers WHERE Customer. ID = 'ALFKI' OR Customer. ID='BOLID' UNION ALL SELECT 2, 1, Customers. Customer. ID, NULL, 'O-'+CAST(Orders. Order. ID AS varchar(32)) FROM Customers INNER JOIN Orders ON Customers. Customer. ID=Orders. Customer. ID WHERE Customers. Customer. ID = 'ALFKI' OR Customers. Customer. ID='BOLID' ORDER BY [Customer!1!cid!id] FOR XML explicit

Updategrams & XML Views Modify the database through the XML View Declare before and after images of the XML (what it is now, and what you want it to be) Optimistic concurrency control ensures that the transaction takes place only if the before image matches the current database state Each updategram is translated into one or more INSERT, UPDATE, and DELETE statements, carried out as a single transaction

Updategrams And XMLViews <updategram-example> <updg: sync mapping-schema=“schema. xsd” xmlns: updg=“urn: schemas-microsoft-com: xml-updategram”> <updg: before> <Customer. ID=“LAZYK” Address=“ 12 Orchestra Terrace”> <Order. ID=“ 27” Unit. Price=“ 100” Quantity=“ 1000” /> </Customer> </updg: before> <updg: after> <Customer. ID=“LAZYK” Address=“ 8 Opera Court”/> </updg: after> </updg: sync> </updategram-example>

Updategrams in real life Enables persisting of changes to XML view by disconnected clients Can easily represent hierarchical changes to data Existing programming model is limited, client must generate updategrams manually

XML Bulkload & XML Views Bulkload XML documents into a SQL Server database via the XML View Just like traditional bulkload, except supports hierarchical relationships and semi -structured data Scriptable COM object Performance is about 75% of traditional bulkload

XML Bulkload & XML Views Sample Bulkload vbs script: set obj. BL = Create. Object("SQLXMLBulk. Load") obj. BL. Connection. String = "provider=SQLOLEDB. 1; data source=(local); database=tempdb; uid=sa; pwd=" obj. BL. Error. Log. File = "error. xml" obj. BL. XMLFragment = True obj. BL. Check. Constraints = False obj. BL. Execute "schema. xsd", "data. xml" Set obj. BL = Nothing

XML Bulkload in real life Very fast, efficient way to shred XML and insert in to a SQL Server database XML Views allows the publisher and consumer of the data to have different database schemas Currently, does not support “max errors” so bulkload processing is stopped by first problem

Data Access Methods HTTP Access via URL SQL Query XPath Query Templates ADO / OLEDB. NET access through SQLXML Managed Classes SOAP

SQLXML Managed Classes Sql. Xml. Command cmd = new Sql. Xml. Command(Conn. Str); cmd. Command. Type = Sql. Xml. Command. Type. XPath cmd. Command. Text = “/Customer[@Cust. ID=‘ALFKI’]”; cmd. Schema. Path = “nwind. xml”; cmd. Root. Tag = “ROOT”; cmd. Xsl. Path = “Customers. xsl”; Xml. Reader r = cmd. Execute. Xml. Reader(); // or Data. Set ds = new Data. Set(); Sql. Xml. Adapter ad = new Sql. Xml. Adapter(cmd); ad. Fill(ds); //… ad. Update(ds); // Data. Set generates diffgram

XML View Application Scenario – A data driven web site Problem: Need to publish data driven web site with minimal coding Control of SQL Server database api (table, stored procs, views) is limited Solution: Define an XML View and apply XSLT to the instance documents to generate HTML User communicates with web site through template parameters

XML View Application Scenario – Disconnected Clients: Problem: Client application is required to consume data from different SQL Server databases Client may need disconnected, HTTP access to SQL Server Solution: Define common XML View Client queries with XPath, and submits changes via updategrams using SQL Server HTTP access

XML View Application Scenario – Data Interchange: Problem: Want to exchange large amounts of data with other applications Need to decouple the logical business from the database schema Solution: Run XPath queries against XML View to generate data Incrementally load into destination database using XML View (different mappings) and XML Bulkload

demo Using the Xml View to decouple business objects from database

A decoupled architecture Class Employee { <Employee> int ID, string last. Name, string first. Name, … <ID>23</ID> Xml Serialization } <last. Name >Jones</last. Name> > <last. Name>Jones</ <first. Name >Jeff</first. Name> > <first. Name>Jeff</ <Employee. Address > <Employee. Address> <street>122 Oak</street> Class Employee. Address <city>Tacoma</city> { <state>WA</state> string street, string city, string state, Sql. Xml SQL Server </Employee. Address > </Employee. Address> </Employee> …. } CLR Business Objects XML Relational Database

Demo is available online Complete Demo available at MSDN Extreme Xml columnhttp: //msdn. microsoft. com/columns/xml. asp April column – “Death, Taxes and Relational databases, Part 1” uses server side technologies Open. Xml and For Xml. July column will use Annotated Mapping schemas and updategrams. Extreme Xml column contains Sql. Xml specific column approximately four times a year.

Other Sql. Xml resources Download latest Sql. Xml release at http: //www. msdn. com/sqlxml Learn about product via documentation and supporting material at Sql. Xml books “The Guru's Guide to SQL Server(TM) Stored Procedures, XML, and HTML” - Pearson Education “Programming Microsoft SQL Server 2000 with XML”, Second Edition – Microsoft Press

Sql. Xml Community Resources http: //msdn. microsoft. com/sqlxml Newsgroups microsoft. public. sqlserver. xml microsoft. public. dotnet. xml http: //www. Sql. Xml. org Sql. Xml Blog http: //blogs. gotdotnet. com/aconrad

Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx

Ask The Experts Get Your Questions Answered After this talk ATE – Thursday July 3 rd 11: 00 -14: 00

evaluations

© 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.