XML and SQL Server

  • Published on

  • View

  • Download


XML and SQL Server


  • 1xml and sql server

  • 2sql server and xml

    The way to present data in a platform independent manner is through XML. The client-cursor, SQLOLEDB provider, and SQL Server internally all provide XML support.

  • 3why databases and xml?

    There are many reasons for integrating XML format and relational databases Most data is stored in a database for administrative and

    concurrency reasons XML can be used as a method of data exchange XML has become a popular data representation method XML can represent hierarchical composition of multiple

    relational tables XML data can be decomposed into multiple relational tables

  • 4sql server and xml choices

    Many facets of SQL Server and XML integration SQL Server 2000 has built-in XML functionality SQLOLEDB 2.6 provider enables XML query dialects and

    streamed input and output ISAPI application enables HTTP access XML for SQL Server Web Release 1 adds updategrams and

    XML bulk load SqlClient data provider supports extended XML function XML for SQL Web Release 2 adds client-side XML, XSD

    support and .NET support of SQLOLEDB 2.6 functionality

  • 5xml support in sql server 2000

    SQL Server 2000 includes internal XML support SELECTFOR XML queries return the resultset as a

    stream of XML sp_xml_preparedocument extended stored procedure

    parses a VARCHAR variable containing XML, returns a document handle

    OpenXML function uses the document handle to produce a rectangular resultset

  • 6select for xml

    SQL Server 2000 has some XML query capability built in "FOR XML" on the SELECT statement is an extension to

    Transact-SQL Three different variations of XML output (RAW, AUTO and

    EXPLICIT) Produces streamed single-column XML as output Element-normal form and XDR schema/DTD inclusion is


  • 7for xml variations

    Different "FOR XML" variations server different purposes RAW mode produces one set of elements, even when

    using JOIN AUTO mode produces hierarchies with separate nested

    elements for each JOINed table EXPLICIT mode produces arbitrary shapes using UNION

    queries and special columns and column names

  • 8elements, attributes and data values

    SQL column values can be stored in elements or attributes All data in elements/attributes known as element/attribute

    normal form XML RAW always produces attribute-normal form AUTO can produce element-normal or attribute-normal form EXPLICIT can produce mixtures of elements and attributes

  • 9-- this query:SELECT Customers.CustomerID, Orders.OrderIDFROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerIDORDER BY Customers.CustomerIDFOR XML RAW

    -- produces this XML output document fragment

    XML RAW query and output

  • 10

    -- this query:SELECT Customers.CustomerID, Orders.OrderIDFROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerIDORDER BY Customers.CustomerIDFOR XML AUTO

    -- produces the following XML document fragment

    XML AUTO query and output

  • 11

    -- this query:SELECT 1 as Tag, NULL as Parent,

    Customers.CustomerID as [Customer!1!CustomerID],NULL as [Order!2!OrderID]

    FROM CustomersUNION ALLSELECT 2, 1,


    FROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerIDORDER BY [Customer!1!CustomerID]FOR XML EXPLICIT

    -- produces this output document fragment

    FOR XML Explicit format

  • 12

    storing xml in sql server

    Stored procedures and UDF can assist decomposition of XML documents System stored procedure parse an XML document into a

    "DOM handle" OpenXML function produces a rectangular resultset from

    XML Nodesets of the XML document are mapped to columns with

    XPath mappings Can use INSERT...SELECT or UPDATE/DELETE to insert

    document nodesets

  • 13

    declare @idoc intdeclare @doc varchar(1000)set @doc ='


    exec sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM OpenXML (@idoc, '/ROOT/Customers/Orders/OrderDetails',2)WITH (CustomerID varchar(10) '../@CustomerID',

    ProdID int '@ProductID',Qty int '@Quantity')

    exec sp_xml_removedocument @idoc

    Using OpenXML and column patterns

  • 14

    DECLARE @h intDECLARE @xmldoc varchar(1000)

    set @xmldoc ='


    EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

    INSERT INTO storesSELECT * FROM OpenXML(@h,'/root/stores')WITH stores

    INSERT INTO discountsSELECT * FROM OpenXML(@h,'/root/stores/discounts')WITH discounts

    EXEC sp_xml_removedocument @h

    Decomposition using OpenXML

  • 15

    sqloledb and xml

    SQLOLEDB provider in OLE DB 2.6 enables XML features XML functionality separated into SQLXMLX.DLL Command coclass supports streamed input and output Extra properties on Command enable stylesheets and

    database/XML mappings Two new query dialects enable XML format queries

    MSSQLXML wraps SQL or XPath queries in XML (requires streamed input)

    XPath queries are permitted as strings

  • 16

    SELECT Customers.CustomerID, Orders.OrderIDFROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerIDORDER BY Customers.CustomerIDFOR XML RAW


    MSSQLXML input documents

  • 17

    using xpath queries

    Support of XML XPath query language requires two pieces Mapping schema maps tables, columns, etc. to a "virtual

    XML document" SQLOLEDB 2.6 mapping schemas are XDR; WebRelease 2

    adds support for XSD XPath query is applied to virtual document to produce output Templated and string based XPath queries supported

    Internally, XPath + Mapping Schema is translated to "SELECT FOR XML EXPLICIT" SQL query by SQLOLEDB

  • 18

    An XDR mapping schema

  • 19

    Sub DoXPath(xmlDoc as MSXML.DOMDocument)Dim conn as New ADODB.ConnectionDim cmd as New ADODB.CommandDim stmOut as New ADODB.Stream

    conn.Open "provider=sqloledb;uid=sa;initial catalog=pubs"Set cmd.ActiveConnection = connstmOut.Open

    ' xmlDoc contains XPath query from next slideSet cmd.CommandStream = xmlDoc

    ' Set XPath dialect, output stream and mappingscmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"cmd.Properties("Output Stream") = stmOutcmd.Properties("Base path") = "c:\schemas"cmd.Properties("Mapping schema") = "mappings.xdr"

    ' Return XML result in output streamcmd.Execute , , adExecuteStreamEnd Sub

    Using an XML mapping schema (ADO)

  • 20

    -- query input


    -- returned document

    Using an XML mapping schema - XPath query and results

  • 21

    sqlxmloledb provider

    Web Release 2 includes new OLE DB provider SQL Server 2000 handles "FOR XML" inside SQL Server;

    SQLXMLOLEDB provider exposes analogous functionality on client

    Preprocesses "FOR XML" queries so only raw SQL is submitted to SQL Server

    Post-processes SQL resultset in XML format on client SQLXMLOLEDB is an OLE DB service provider - currently

    only supports SQLOLEDB as underlying provider Enables SQL Server 2000-like support for SQL Server 7

  • 22

    iis support for database driven xml

    Any version of SQL Server can directly export XML through IIS SQL Server 2000 uses native "FOR XML" clause and

    SQLOLEDB provider HTTP GET and POST supported Query templates enable fixed and parameterized queries

    XPath and SQL supported Transactional XML Updategrams in WR1 Client-side XML is supported in WR2 through

    SQLXMLOLEDB provider Client-side XML enables using "FOR XML" queries with SQL

    Server 7 or 2000

  • 23

    Using FOR XML and SQL Server 2000


    Submit through SQLOLEDB

    Add stylesheet



  • 24

    Using SQLXMLOLEDB provider


    Format SQL from XMLSubmit by SQLXMLOLEDB

    Format output XMLAdd stylesheet, DTD, schema


  • 25


    Updategrams use XML format to update SQL Server Supported in Web Release 1 through ISAPI program Insert/Update/Delete consist of XML-format before and after

    images SQLOLEDB converts these XML format to SQL Batching of updates and transactions are supported Web Release 2 supports .NET Diffgrams

  • 26



    Updategram in element-centric mode

  • 27


    Updategram in mixed mode

  • 28

    xml bulk loader

    Web Release 1 includes XML bulk load facility OpenXML requires too much memory for bulk load of large

    XML documents Bulk load exposed through separate COM object Bulk load analogous to import through DTS or BCP

  • 29

    set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString =


    objBL.ErrorLogFile = "c:\error.log"objBL.Execute "c:\SampleSchema.xml", "c:\SampleData.xml"set objBL=Nothing

    XML Bulk Load through the bulk load class

  • 30

    sqlclient, datasets and diffgrams

    Three integration points in native .NET libraries SQLISAPI program can be used directly as a web endpoint SqlClient data provider exposes "FOR XML" results using

    SqlCommand.ExecuteXmlReader Resulting XmlReader can be used for DataSet input

    producing multiple DataTables DataSets can export two variations of Diffgram format

    Entire DataSet and changesOnly changed rows

  • 31

    SqlCommand cmd = new SqlCommand("select * from authors for xml auto",new SqlConnection("server=localhost;uid=sa;database=pubs"));


    XmlTextReader rdr;rdr = (XmlTextReader)cmd.ExecuteXmlReader();

    DataSet ds = new DataSet();ds.ReadXml(rdr, XmlReadMode.Fragment);

    Using SQLXML through a ExecuteXmlReader

  • 32

    DataSet ds = new DataSet();XmlTextReader rdr = new XmlTextReader(



    Using SQL Server 2000 ISAPI application

  • 33

    SqlDataAdapter da = new SqlDataAdapter("select * from sample","server=localhost;uid=sa;database=pubs");

    DataSet dsAll = new DataSet("all");DataSet dsChanges = new DataSet("changes");

    da.Fill(dsAll, "sample");DataTable t = dsAll.Tables["sample"];

    DataRow r = t.NewRow();r[0] = 4;r[1] = "Shemp";t.Rows.Add(r);

    t.Rows[1]["name"] = "CurleyJoe";t.Rows[2].Delete();

    dsAll.WriteXml("c:\\all.xml", XmlWriteMode.DiffGram);dsChanges = dsAll.GetChanges();dsChanges.WriteXml("c:\\changes.xml", XmlWriteMode.DiffGram);

    Producing DiffGrams

  • 34


    Web Release 2 includes SQLOLEDB-XML functionality SqlXml data provider exposes Command, Parameter and

    DataAdapter All SQLOLEDB 2.6 and above functionality supported

    MSSQLXML and XPath queriesXSL transforms

    DataAdapter enables integration with DataSet and DiffGrams

  • 35

    Using SqlXmlCommand and XSD mapping schema

    void doXPath(){Stream strm;SqlXmlCommand cmd = new SqlXmlCommand("provider=sqloledb;uid=sa;database=northwind");cmd.CommandText = "x:Employee[@EID='1']";cmd.CommandType = SqlXmlCommandType.XPath;cmd.RootTag = "ROOT";cmd.Namespaces = "xmlns:x='urn:myschema:Employees'";cmd.SchemaPath = "c:\\xml_mappings\\MySchemaNS.xml";strm = cmd.ExecuteStream();StreamReader sw = new StreamReader(strm);Console.WriteLine(sw.ReadToEnd());}

  • 36

    Using SqlXmlCommand and XSD mapping schema

  • 37

    Using XSLT and SqlXmlCommand output

    Stream strm;SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);cmd.CommandText ="select FirstName, LastName from Employees For XML Auto";

    cmd.RootTag = "root";// get output Streamstrm = cmd.ExecuteStream();

    // line up transform and documentXmlTextReader reader = new XmlTextReader(strm);XPathDocument xd = new XPathDocument(reader, XmlSpace.Preserve);XslTransform xslt = new XslTransform();xslt.Load("c:\\xml_mappings\\MyXSL.xsl", null);XmlTextWriter writer = new XmlTextWriter(


    // transform stream outputxslt.Transform(xd, null, writer);

  • 38


    SQL Server 2000 includes native XML support SQLOLEDB provider contains additional support ISAPI DLL exposes SQL/XML as HTTP endpoints Updategrams and Bulk Load added in Web Release 1 Native .NET support in Web Release 2


View more >