Xml and databases

Xml and databases

• 1.
  XML and Databases
• 2.
  ReadingXML Data from Databases  Althoughspecific to the OLE DB specification, the term rowset is  often used generically to indicate a set of rows that contain columns of data. Rowsets are key objects that enable all OLE DB data providers to expose query result set data in a tabular format.  The FOR XML extensions let you consider XML mostly as a data output format.With the alternative technique for retrieving data as XML, you can store raw XML data in a text or BLOB field and retrieve that data using an ordinary query—preferably a scalar,  single-field query. In both cases, the MicrosoftADO.NET object model, along with the Microsoft .NET Framework XML core classes, provide a number of handy features to extract XML data quickly and effectively
• 3.
   Access SQLServer through a URL  Create XML schema-driven views of relational data.  Return fetched data as XML  Insert data represented as an XML document.
• 4.
  The FORXML AUTO Mode The AUTO mode returns data packed as XML fragments—that is, without a root node.The alias of the table determines the name of each node. If the query joins two tables on the value of a column, the resulting XML schema provides nested elements.  Let's consider the following simple query: SELECT CustomerID, ContactName FROM Customers FOR XML AUTO The XML result set has the form shown here: <Customers CustomerID="ALFKI" ContactName="MariaAnders" /> <Customers CustomerID="ANATR" ContactName="AnaTrujillo" /> ... Try now with a command that contains an INNER JOIN, as follows: SELECT Customers.CustomerID, Customers.ContactName, Orders.OrderID FROM Customers
• 5.
   INNER JOINOrders ON Customers.CustomerID = Orders.CustomerID FOR XML AUTO  <Customers CustomerID="ALFKI" ContactName="Maria Anders">  <Orders OrderID="10643"/>  <Orders OrderID="10692"/>  <Orders OrderID="10783"/>  ...  </Customers>  <Customers CustomerID="ALFKI" ContactName="Ana Trujillo">  <Orders OrderID="11459"/>  <Orders OrderID="10987"/>  ...  </Customers>  ...
• 6.
   The XMLoutput is similar to this:  <Customers>  <CustomerID>ALFKI</CustomerID>  <ContactName>MariaAnders</ContactName>  </Customers>  <Customers>  <CustomerID>ANATR</CustomerID>  <ContactName>AnaTrujillo</ContactName>  </Customers>  ...
• 7.
  The client-side XML formatting featureof SQLXML 3.0 makes use of intermediate OLE DB providers to execute the query and transform the results
• 8.
  CreatingXML Views  Just asa CREATEVIEW statement in SQL lets you create a virtual table by collecting columns from one or more tables, an XML view provides an alternative and highly customizable way to present relational data in XML.
• 9.
   Building anXML view consists of defining a custom XML schema and mapping to its elements the columns and the tables selected by the query. Once built, an XML view can be used like its close cousin, SQL view. In particular, an XML view can be queried  using XPath expressions and transformed using XSL Transformation (XSLT) scripts. An XML view is simply a stream of XML data and can be used as allowed by .NET. In the .NET Framework, you can use XML views through readers, XML DOM, or even specialized classes, such as those in SQLXML 3.0.
• 10.
   There aretwo possible ways to create XML views: you can use the FOR XML EXPLICIT mode of the SELECT statement, or you can build an annotated XDR or XSD schema.To use an XSD schema, you must install SQLXML 3.0 first
• 11.
  The FORXML EXPLICIT Mode  Thequery defines the shape of the generated XML document.The ultimate goal of the query is making hierarchical data fit into a tabular rowset. An EXPLICIT mode query creates a virtual table in which all the information fetched from the tables is organized in such a way that it can then be easily rendered in XML.
• 12.
   <Employee id="employeeid" name="titleOfCourtesy lastname, firstname">  <PersonalData>  <Birth>birthdate</Birth>  <City>city</City>  </PersonalData>  <JobData>  <Hired>hiredate</Hired>  <Title>title</Title>  </JobData>  <Notes>notes</Notes>  </Employee>
• 13.
   <xsd:schema  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  <xsd:element name="Employee" sql:relation="Employees" >  <xsd:complexType>  <xsd:sequence>  <xsd:element name="FName“  sql:field="FirstName" type="xsd:string" />  <xsd:element name="LName"  sql:field="LastName" type="xsd:string" />  </xsd:sequence>  <xsd:attribute name="EmployeeID" type="xsd:integer" />  </xsd:complexType>  </xsd:element>  </xsd:schema>
• 14.
  XML Data Readers  .NETFramework applications delegate all their low-level data access tasks to a special breed of connector objects called managed data providers.The object model around these connector components is known as ADO.NET. Basically, a data provider is the  software component that enables any .NET Framework pplication to connect to a data source and execute commands to retrieve and modify data.
• 15.
   A .NETFramework data provider component interfaces client applications through the objects in the ADO.NET namespace and exposes any provider-specific behavior directly to consumers. A .NET Framework data provider component creates a minimal  layer between the physical data source and the client code, thereby increasing performance without sacrificing functionality.
• 16.
  OLE DB and .NET Framework ManagedData Providers  OLE DB is the data access technology that translates the Universal DataAccess (UDA) vision into concrete programming calls. Introduced about five years ago, UDA describes a scenario in which all the data that can be expressed in a tabular format  can be accessed and manipulated through a commonAPI, no matter the actual binary format and the storage medium. According to the UDA vision, special modules—the  OLE DB providers—would be called to expose the contents of a data source to the world.Another family of components—the OLE DB consumers—would consume such contents by interacting with the providers through a commonAPI.