Microsoft SQL Server 2005 Developer’s Guide- P13

Microsoft SQL Server 2005 Developer’s Guide- P13:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P13 Chapter 7: Developing with XML 239 Brown Kevin Tamburello Roberto For more information about using the XML Explicit mode, see the SQL Server2005 BOL.Type ModeWhen XML data types are returned using the FOR XML clause’s Type mode, theyare returned as XML data types. You can see an example of using the FOR XMLclause with the XML Type directive here:SELECT DocID, MyXMLDoc FROM MyXMLDocs WHERE DocID=1 FOR XML AUTO, TYPE NOTE This listing uses the example MyXMLDocs table that was created earlier in this chapter. This query returns the relational DocID column along with the MyXMLDoc XMLdata type column. It uses the FOR XML AUTO clause to return the results as XML.The TYPE directive specifies that the results will be returned as an XML data type.You can see the results of using the Type directive here:-------------------------------------------------- 1 Modified Body (1 row(s) affected)240 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e NOTE The preceding listing was reformatted to make it more readable in the published page width. FOR XML Path The new FOR XML PATH mode provides increased power to shape XML results than either the FOR XML AUTO or FOR XML RAW mode but without the complexity of the FOR XML EXCLICIT mode. The new PATH mode allows users to specify the path in the XML tree where an element or attribute can be added. Essentially, the new PATH mode is a simpler alternative to the FOR XML EXCPLICIT mode. It can accomplish most of the things the developers need with the use of universal tables and complex unions. However, it is more limited than the FOR XML EXPLICIT mode. You can see an example of using the FOR XML PATH mode in the following listing: SELECT Top 3 title, FirstName, LastName from Person.Contact FOR XML PATH This query uses the same Person.Contact table from the AdventureWorks database that the earlier FOR XML RAW and AUTO modes did, but with quite different results, which you can see here: Mr. Gustavo Achong Ms. Catherine Abel Ms. Kim Abercrombie By default each of the results is enclosed in the set of tags. The output is close to the output that can be produced using FROM XML EXPLICIT. Chapter 7: Developing with XML 241However, the FOR XML PATH statement provides additional flexibility by makingit possible to insert attributes and elements, enhancing the structure of the output.The following list shows how you can add the element to this outputusing the For XML PATH mode:SELECT Top 3 title Employee/Title, FirstName Employee/First_Name, LastName Employee/Last_Name from Person.Contact FOR XML PATH Much as when you use a standard SQL AS clause, you can add parent tags andrename the XML output elements by using the quoted string that you can see followingeach column in this FOR XML PATH example. The Employee tag, which you can seeto the left of the / symbol, will be created when the result set is output. The name tothe right of the / symbol will be used as a new name for the element. The output fromthis version of the FOR XML PATH mode can be seen in the following listing. Noticewhere the tag has been added to the XML output: Mr. Gustavo Achong Ms. Catherine Abel Ms. Kim Abercrombie 242 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Nested FOR XML Queries SQL Server 2000 was limited to using the FOR XML clause in the top level of a query. Subqueries couldn’t make use of the FOR XML clause. SQL Server 2005 adds the ability to use nested FOR XML queries, which are useful for returning multiple items where there is a parent-child relationship. One example of this type of relationship might be order header and order details records; another might be product categories and subcategories. You can see an example of using a nested FOR XML clause in the following listing: SELECT (SELECT title, FirstName, LastName FROM Person.Contact FOR XML RAW, TYPE,ROOT(root)).query(/root[1]/row[1]) Notice that the inner SELECT statement uses the TYPE mode to return a XML result. This result is then processed using a simple XQuery executed with the XML data type’s query method. In this case the XQuery extracts the values from the first row in the result set, as is shown here: Inline XSD Schema Generation SQL Server 2005’s FOR XML support also has the ability to generate an XSD schema by adding the XMLSCHEMA directive to the FOR XML clause. You can see an example of using the new XMLSCHEMA directive in the following listing: SELECT MyXMLDoc FROM MyXMLDocs WHERE DocID=1 FOR XML AUTO, XMLSCHEMA In this case, because the XMLSCHEMA directive has been added to the FOR XML clause, the query will generate and return the schema that defines the specific XML column along with the XML result from the selected column. The XMLSCHEMA directive works only with the FOR XML AUTO and FOR XML RAW modes. It cannot be used with the FOR XML EXPLICIT or FOR XML PATH mode. If the XMLSCHEMA directive is used with a nested query, it can be used only at the top level of the query. The XSD schema that’s generated from this query is shown in the following ...