XML and SQL Server 2000

1. XML and SQL Server 2000

2. Introduction • With the introduction of Microsoft's XML SQL Server Technology, a host of XML functionality is now available. • Using XML features in SQL 2000, we can • Retrieve SQL data into XML documents • Insert XML data into SQL Server tables.

3. 3 Ways to Access SQL Server And Retrieve Records In XML Format • URLs to access SQL Server through HTTP • The FOR XML clause in the SELECT statement to retrieve results in XML format • System stored procedures for manipulating XML data

4. Accessing SQL Server Through HTTP • Two ways to do so: • Enter an SQL statement into a URL in Microsoft Internet Explorer (IE) 5.0 and retrieve records in XML format • Use a template • http:///?sql=+FOR+XML+RAW • First, the query uses the HTTP protocol. The query then directs its request to the Web server, Microsoft Internet Information Server (IIS), through a virtual root on the IIS server that you must configure to use the SQL Server XML extensions. • Next, the query requests the data you want.

5. Accessing SQL Server Through HTTP • You can also store template queries in a file in the virtual directory and reference them as follows: (if query is too long or for security purpose) • http://auril/Northwind/myquery.xml • The myquery.xml query asks for a given customer ID's customer order information and looks like <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:query CustomerID='ALFKI'> SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM ORDERS WHERE CustomerID=? ORDER BY OrderID XML RAW </sql:query> </ROOT>

6. FOR XML • Using FOR XML clause in a SELECT statement lets you retrieve results as an XML document instead of a row set. 3 Arguments: • SchemaOption.This option specifies that the database return a schema. SchemaOption DTD or XMLData. DTD returns the schema and adds the root element to the results. XMLData returns the schema but doesn't add the root element. • ELEMENTS. With this option, the SELECT statement returns columns as subelements. Otherwise, the statement maps the columns to XML attributes. SQL Server 2000 supports this option only in AUTO mode. • XML mode. specifies the XML mode (RAW, AUTO, or EXPLICIT), which determines the shape of the resulting XML tree.

7. XML Modes EXAMPLE: • SELECT store.stor_id as Id, stor_name as Name, sale.ord_num as OrderNo,sale.qty as Qty FROM stores store inner join sales sale on store.stor_id = sale.stor_id ORDER BY stor_name FOR XML <MODE> • The query generates a result table that contains all the sales and the stores from which those sales were made, in ascending alphabetical order. RESULT: • <Stores><Store Id='' Name=''> </Sale OrderNo='' Qty=''> </Store><Stores>

8. RAW Mode RAW ModeThe XML document produced contains an element <Row>, which is fixed, for each record of the result set generated by Figure 1. This is not very useful because we have no control over the element naming and document structure.

9. Auto Mode • AUTO ModeLook at the output structure generated by the AUTO mode. As you can see the <STORE> and <SALE> tags have a parent-child relationship, giving us the hierarchical structure we require. • This node relationship is determined on the order in which the tables are declared within the query, with each table declared in the sequence becoming a child of the previously declared table.

10. Auto Mode(cont.) Disadvantages: • We can create hierarchical structures, but only in a linear fashion, as a parent node can only have one child and vice versa. • We cannot have both attributes and elements within the document. Its either all elements, specified using the ELEMENTS keyword, or the default, which is attributes

11. EXPLICIT MODE • An alternative method allows us to fully manipulate the XML document generated. • Explicit Mode enable us to explicitly specify how we want the element nesting to appear in the XML output document. • SELECT 1 Tag, NULL Parent, FieldName1, FieldName2,… FROM TableName UNION ALL SELECT 2 Tag, NULL Parent, FieldName1, FieldName2,… FROM TableName FOR XML EXPLICIT

12. Universal Table in Explicit Mode

13. EXPLICIT MODE • Key relationships within the table that define the hierarchy of the resulting XML tree: • If the parent is 0 or NULL, the associated row is the top level of the XML tree. • The columns in the Universal table define groups. Each of these groups becomes an XML element in the resulting document.

14. EXPLICIT MODE • Example: XML document generated from Universal table. <Employee eid=“5” name=“Nancy”> <Order id=“60185” date=“01/01/2001”> <OrderDetail id=“0D1” pid=“p1”/> <OrderDetail id=“0D1” pid=“p2”/> </Order> <Order id=“02” date=“03/29/2002”>… </Employee>

15. EXPLICIT MODE • Syntax: ElementName!TagNumber!AttributeName!Directive • How to use explicit mode: • Create the first two columns of the universal table, Tag and Parent. Top element with tag number 1 and parent NULL. Next level will have Tag number 2 and parent is 1 and so on. • Each level is a SQL statement. All Select statements are combined using UNION ALL. • Add FOR XML EXPLICIT clause to create XML output.

16. EXPLICIT MODE • More Example: The document we want to create: <Orders OrderID=“1028”> <OrderDetails ProductID=“11”/> <OrderDetails ProductID=“42”/> <OrderDetails ProductID=“72”/> <Orders/> <Orders OrderID=“10249”> <OrderDetails ProductID=“14”/> <Orders/> …

17. EXPLICIT MODE • Select Statement: SELECT 1 as Tag, NULL as Parent, Orders.OrderID as [Orders!1!OrderID], NULL as [OrderDetails!2!ProductID] FROM Orders UNION ALL SELECT 2, 1, Orders.OrderID, OrderDetails.ProductID FROM Orders, OrderDetails WHERE Orders.OrderID=OrderDetails.OrderID ORDER BY [Orders!1!OrderID], [Orders!2!ProductID] FOR XML EXPLICIT

18. EXPLICIT MODE • Partial Universal Table Tag Parent [Orders!1!OrderID] [OrderDetails!2!ProductID] 1 NULL 10248 NULL 2 1 10248 11 2 1 10248 42 2 1 10248 72 1 NULL 10249 NULL 2 1 10249 14

19. System Stored Procedures • SQL Server 2000 adds six system stored procedures • sp_xml_preparedocument, ~let you store XML • sp_xml_fetchdocument, ~ retrieve XML using a pointer • sp_xml_removedocument, ~ delete an entire document • sp_xml_removexml, ~ specify elements within the document to delete • sp_xml_insertfromxml, ~ insert into an XML document from a table • sp_xml_fetchintoxml. ~ insert into a table from an XML document

20. System Stored Procedures LISTING 2: XML-Enabled Northwind Stored Procedure CREATE PROCEDURE CustOrdersOrdersXML @CustomerID nchar(5) AS SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID FOR XML RAW GO

21. OpenXML • OpenXML is a command in SQL to allow writing data to a database table from an XML document. • OpenXML keyword causes XML document to be presented as a data source similar to a view or a table. • This is accomplished in memory by making the XML document appear as a relational rowset. • The relational rowset is stored into database tables.

22. OpenXML

23. OpenXML When? -OpenXML can be used whenever a rowset is necessary, such as a table or view. (Select, Select Into, and OpenRowset.) How? -sp_xml_PrepareDocument takes an XML document and parses it into a memory representation of the XML document tree.

24. OpenXML -sp_xml_preparedocument returns a file identifier (handle) after parsing it in memory. -Parsed document is then written into memory. -OpenXML takes this handle and, through a process known as shredding, provides a rowset representation of the data based on what is passed to it. --sp_XML_RemoveDocument is called to flush the parsed document from memory.

25. OpenXML Syntax for OpenXML: OpenXML(handle, ‘rowpattern’,[flags]) [ With SchemaDeclaration] • Handle: returned by sp_xml_prepareDocument that points to the parsed XML document in SQL Server memory. Valid until the connection is reset or sp_removeDocument is executed.

26. OpenXML • RowPattern: Similar to XPath expression. For every node identified by the XPath expression there is a row generated by OpenXML in the rowset. • Flags: (1, 2, or 3) 1: attribute-centric mapping. 2: element-centric mapping. 3: both attribute and element-centric mapping. -Flags and colPattern are optional parameters and , if missing, cause the default value of attribute-centric mapping to be used.

27. OpenXML • WITH SchemaDeclaration • Use WITH clause and define the entire schema. SELECT * FROM OPENXML (@idoc, ‘/ROOT/Employee/Order/OrderDetail’,2) WITH ( OrderID int ‘../@OrderID’, CustomerID varchar(30) ‘../@CustomerID’, LastName varchar(30) ‘./../@LastName’, ….) • Use an existing table identified in the WITH clause. SELECT * FROM OPENXML (@idoc, ‘/ROOT/Employee/Order’,1) WITH Table1

28. OpenXML • DECLARE @idoc int • DECLARE @xmldoc varchar(1000) • SET @xmldoc =' • <ROOT> • <Employee EmployeeID="5" LastName="Buchanan" FirstName="Steven"> • <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" • OrderDate="1996-07-04T00:00:00"> • <OrderDetail ProductID="42" Quantity="10"/> • </Order> • </Employee> • <Employee EmployeeID="7" LastName="King" FirstName="Robert"> • <Order OrderID="10303" CustomerID="GODOS" EmployeeID="7" • OrderDate="1996-09-11T00:00:00"> • <OrderDetail ProductID="40" Quantity="40"/> • </Order> • </Employee> • </ROOT>' • -- Create an in memory representation of the document. • EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc • SELECT * • FROM OPENXML (@idoc, '/ROOT/Employee',1) • WITH (EmployeeID varchar(4), • LastName varchar(20), • FirstName varchar(10)) • EXEC sp_xml_removedocument @idoc

29. OpenXML The result of the select above: