Querying Metadata
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Querying Metadata as PDF for free.
More details
- Words: 6,539
- Pages: 20
Querying Metadata Categories of Data If your data is highly structured with known schemas, then the relational model might be the best form of data storage. However, if the structure of your data is flexible or unknown, then you need to determine other techniques to store such semi-structured or unstructured data. SQL Server 2005 provides the necessary functionalities and tools to handle structured, semi-structured, and unstructured data.
Data Category
Structured data is organized in semantic groups or entities. Similar entities are grouped together by using relations or classes. Entities in the same group have the same descriptions or attributes. Also, descriptions can be provided for all entities in a group by using a schema. Structured data is typically stored in a Relational Database Management System (RDBMS) and is also referred to as relational data. The data is stored in the form of rows and columns. The columns allow you to constrain the type and the amount of data that can be stored. Structured data provides you various advantages. You can normalize your data into multiple tables, thereby reducing redundancy. You can also create efficient data structures to minimize data size. Besides this, it is possible to control data elements by using various constraints and enforce business rules for data integrity. However, structured data has a few disadvantages as well. It is difficult to change the structure of the data when your application changes drastically. Data with many variable input parameters can result in the creation of many columns holding just NULL values. Because the data is stored in a proprietary binary format, it cannot interact directly with other external systems. Semi-structured data is data that is available electronically in file systems or data exchange formats such as bibliographic data or scientific data. Semi-structured data is often organized in semantic entities where similar entities are grouped together. However, entities in the same group may not have the same attributes. XML is a widely used form of semi-structured data. Semi-structured data is suitable for scenarios where the structure of the data is not known completely or is likely to change significantly in future. Data can be retrieved in exactly the same order that it was stored. Because XML is an open standard, the data can be used by multiple systems running on disparate platforms without any changes. Security can be implemented by assigning several levels of permissions for different users. XML is also useful for data interchange among loosely coupled, disparate systems, such as B2B applications and workflow applications. XML is a suitable form of data storage in document management applications, where most documents such as e-mail messages and reports are semi-structured. Using XML has a few disadvantages as well. Every unit of data has to be marked up with tags and this increases storage space. This also increases the amount of network bandwidth required to transfer a unit of data from one system to another. Because XML data is represented in a text-based format, the data may be tampered with. Also, constraints are more relaxed. Unstructured data could be of any type. It may not comply with standards and is not predictable. Examples of unstructured data could include text, videos, sound, images, e-
mail messages, memos, user groups, chats, reports, letters, surveys, white papers, marketing materials, research, presentations and Web pages. Unstructured data provide a few advantages. The format of the data can be changed at any time without any major effort. The data can be represented in various ways to suit application requirements. Also, you can store multiple formats of data in the same document. Unstructured data has many disadvantages. You cannot easily define the various elements of the data because it is generally stored as a series of values delimited by markers, for example, comma separated values (CSVs). Security cannot be defined on text data. Because you cannot have an indexing mechanism, performance can be slow if the size of the data is very large. What Is Metadata? Metadata is defined as data about data. The users access metadata to view additional and technical information. Metadata views provide an internal, system table-independent view of the SQL Server metadata, and contain all data objects stored in that particular database. Metadata consists of two categories namely, business-oriented metadata and technical metadata.
Metadata Categories Metadata is generally used to provide users with additional information about the data that they access or to hide the complexity of the technical details of the data that they view. It can also be used by systems to determine data types, the time the data was last changed, and what kind of changes were made to the data. Metadata can be used by applications and systems to perform type checking, data validation and auto formatting of data. Metadata consists of two categories: businessoriented metadata, and technical metadata. Business-oriented metadata includes metadata that a user can access such as the table names, column names, column descriptions, formatting rules, or business definitions of data elements. By contrast, technical metadata contains information that supports the back office functions and the other built-in capabilities offered by SQL Server 2005 through components such as SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services. Metadata Views In SQL Server 2005, metadata is used throughout the platform products, starting with the database engine to other servers and applications. Each server exposes metadata programmatically, as well as through the functionalities included in the design and administrative tools. In SQL Server 2005, metadata is stored in hidden resource tables, which can be accessed directly by the server. You can use the new catalog views or Dynamic Management Views (DMVs), which show data obtained from both the hidden resource tables and the various system functions. The system tables can be implemented by using compatibility views for providing backward compatibility with earlier versions of SQL Server. Besides this, there are more than 200 catalog views, INFORMATION_SCHEMA views, and management views in SQL Server 2005. These views provide simple details such as the database structure for complex information like the amount of processor time a query requires to be executed.
How to Query Metadata by Using SQL Server Views SQL Server exposes metadata by using a number of relational objects like INFORMATION_SCHEMA views, Dynamic Management Views, and catalog views. By using these SQL Server views, you can query metadata. Dynamic management Views and catalog views are new features introduced in SQL Server 2005. INFORMATION_SCHEMA views, in SQL Server 2005, contain the metadata of all the data objects in the database. Information schema views provide an internal, system-table independent view of SQL Server metadata. This permits the applications to work correctly though significant changes have been made to the system tables in SQL Server 2005. However, INFORMATION_SCHEMA views provide limited information. If you require detailed information, you can use catalog views. The information schema views included in SQL Server conform to the ANSI standard as well as the SQL-92 Standard definition for INFORMATION_SCHEMA.
Dynamic Management Views (DMVs) return server state information that can be used to monitor the server, check for problems, and to facilitate performance. DMVs return specific data that can change in the future releases of SQL Server. The two types of DMVs are server-scoped DMVs and database-scoped DMVs. All DMVs and functions exist in the sys schema and follow the naming convention dm_*. You should prefix the name of the view or function by using the sys schema.
Catalog views return information that is used by the SQL Server 2005 database engine. You can use catalog views to query metadata because they transform, and present customized forms of information efficiently. All user-available catalog metadata are exposed through catalog views. Some catalog views inherit rows from other catalog views. For instance, the tables catalog view inherits the objects catalog view. This means that the tables catalog view has the columns that are specific to tables, and also all the columns that belong to the objects catalog view. All catalog views are present in a system-level schema named SYS.
How to Query Metadata by Using SQL Server Commands You can use system functions, Database Console Commands (DBCC), and system stored procedures to access metadata information. System functions perform operations on and return metadata information about the values, objects and settings in SQL Server 2005. The following are some of the system functions.
• • • • • • •
SYSTEM_USER SERVERPROPERTY OBJECTPROPERTY CURRENT_TIMESTAMP CURRENT_USER DATALENGTH ISNUMERIC
The SYSTEM_USER function can be used to return the name of the currently executing context. The following example declares a CHAR variable, stores the current value of SYSTEM_USER in the variable and then prints the value stored in the variable.
USE AdventureWorks DECLARE @sys_usr char(30); SET @sys_usr = SYSTEM_USER; SELECT 'The current system user is: '+ @sys_usr; The following example uses the SERVERPROPERTY function to get the server name and to verify whether the server uses only Integrated Windows Authentication.
USE AdventureWorks SELECT SERVERPROPERTY('servername'), SERVERPROPERTY('IsIntegratedSecurityOnly')
DBCC commands can be used not just for returning various types of information, but also for performing maintenance tasks on a database, index or filegroup, validation operations on databases or tables, and also for miscellaneous tasks such as enabling trace flags or removing DLLs from memory. The following are some of the DBCC commands.
• • • • • • • •
DBCC OPENTRAN DBCC USEROPTIONS DBCC CONCURRENCYVIOLATION DBCC SHOW_STATISTICS DBCC CHECKALLOC DBCC CHECKFILEGROUP DBCC SHOWCONTIG DBCC HELP The following example is a DBCC command that returns all the SET options that are active for the current connection.
USE AdventureWorks DBCC USEROPTIONS The following command displays fragmentation information for the HumanResources.Employee table.
USE AdventureWorks DBCC SHOWCONTIG ('HumanResources.Employee')
System stored procedures, in SQL Server 2005, can be used to perform administrative and informational activities. The following are some of the system stored procedures.
• •
sp_column_privileges sp_special_columns
• • • • • • •
sp_cursor_list sp_add_maintenance_plan sp_catalogs sp_send_dbmail sp_fulltext_database sp_spaceused sp_help The following example returns column information for a specific column by using the sp_columns.
USE AdventureWorks GO EXEC sp_columns @table_name = N'Department', @table_owner = N'HumanResources' The following example disables full-text indexing for the AdventureWorks database.
USE AdventureWorks GO EXEC sp_spaceused N'Production.product'; GO
Lesson Introduction The Extensible Markup Language (XML) is a general-purpose markup language recommended by the World Wide Web Consortium (W3C), for creating special-purpose markup languages, capable of describing various kinds of data. XML is a simplified subset of Standard Generalized Markup Language (SGML). The data may be in the form of a record from a data store or an entire document. When you store XML data in a relational database, it provides you the benefits of better data management and query processing. SQL Server provides powerful capabilities to query and modify XML data.
What Is XML? XML provides a text-based means to describe and apply a tree-based structure to information. XML has the ability to represent most data structures such as records, lists and trees. The strict syntax and parsing requirements of XML allow the necessary parsing algorithms to help it remain simple, efficient, and consistent. The XML format is robust, logically-verifiable, and based on international standards. The hierarchical structure is suitable for most types of documents. Interoperability of the data defined in XML format is also another of its benefits. The greatest power of XML is its extensibility. You can invent your own tags with your own semantic meaning. For example, you can create a tag to store customer information data. Your data stream may contain multiple customers and customer information. If you want to find all customers with a specific name, you can do that by searching for the Customer_First_Name tags. An XML document contains various elements. The first line is the XML declaration, an optional line stating what version of XML is in use. An element usually consists of a start tag and a closing tag pair. Between the start tag and closing tag pair, an element may contain data, content, or other elements. An element can contain just the closing tag. The first element that the XML processor encounters must consist of a start tag and a closing tag and is called the root element. All other elements will be within the root element and are called child elements.
A child element can contain child elements of its own. An element can contain attributes. You can use attributes as an alternative to using elements to store data. You can create an attribute in the start tag of an element. You can declare the name of the attribute, followed by a value assignment. You can use either single or double quotation marks to set the value of an attribute. Comments are optional. These are some of the basic rules.
Scenarios Where XML Is Used SQL Server 2005 provides powerful storage, query, and data modification capabilities over XML data. XML data can interoperate with existing relational data and SQL applications. Therefore, XML can be introduced into the system as data modeling needs increase without disrupting your existing applications. Besides these requirements, XML can be useful in many scenarios. Data Exchange XML is a platform-independent format for data representation. So it is useful for exchanging information among loosely-coupled, disparate systems, such as in business-to-business (B2B) applications and workflow situations. Data interchange has been a major advantage of XML technologies. Document Management XML is being increasingly used in enterprise applications for modeling semi-structured and unstructured data. Documents, such as e-mail, are semi-structured in nature. If documents are stored inside a database server in XML format, then powerful applications can be developed that retrieve documents based on document content, query for partial content, and perform document aggregation. Such scenarios are becoming feasible with the rise in applications that generate and consume XML. Messaging XML can be used in a messaging environment, to enable applications to exchange messages with other applications. In Enterprise Application Integration scenarios, when various applications need to be integrated, XML is the format used to interchange data. Mid-tier Collaboration XML can be used for exchanging data between the middle tier and the other tiers in an application. Traditionally the presentation tier sent data to the middle tier in a proprietary binary format. This restricted the kind of technology that could be used to implement the various tiers. Also external applications running on disparate systems could not communicate with a common middle tier component. XML solved these problems by introducing a common standard for representing data. XML Web Services can be effectively used as a middle-tier component that receives and sends all data in XML format. Ad-hoc Modeling XML can be used for ad-hoc modeling of the data. If the structure of the data changes or evolves, the changes can be incorporated in the XML document. Standards such as XPath, XQuery, and XSLT make it very easy to process the data and render it in any other suitable format.
How SQL Server Implements XML SQL Server 2005 provides extensive support for XML data processing. XML values can be stored natively in an XML data type column, which can be typed according to a collection of XML schemas, or left untyped. You can index the XML column. Furthermore, fine-grained data manipulation is supported using XQuery and XML DML, the latter being an extension for data modification. SQL Server implements XML by passing the incoming XML through various processes. First step As the first step in working with XML, SQL Server feeds the incoming XML to an XML Parser. The XML Parser interprets the XML inputs and presents them in the form of a known interface such as a Document Object Model (DOM). XML Parsers also verify that the XML document is wellformed and is syntactically and grammatically valid. XML Parsers can also be used as validating parsers to validate the XML data against a schema. Second step When you provide a schema for the XML documents you will store, you can specify the name of the schema collection that contains the schema that you want to apply to that column. Then,
SQL Server feeds the schema to the XML Parser, which validates the incoming XML against the schema. After this, the validated XML is implicitly converted to the XML data type. Then, the XQuery methods available in SQL Server 2005 such as query(), value(), nodes() or modify() are applied on the converted XML data. Third step The converted XML data can be used for various purposes. You can use the OPENXML nodes method to convert XML data into a rowset format. However, before the XML data is converted to rowsets, SQL Server builds a node table internally. You can apply XQuery methods such as query or value on the contents of the node table. Fourth step To speed up the processing of the data, SQL Server makes use of a primary XML index that can be defined on the column containing XML data. The primary XML index indexes all the tags, values, and paths over the XML instances in the column and benefits query performance. By using the primary XML index, three types of secondary XML indexes - the Path Index, the Property Index and the Value Index – can be created on the XML column to speed up queries. Depending on the scenario and the nature of the XML data, SQL Server uses any of these secondary indexes. Fifth step With these features that provide XML support, SQL Server 2005 provides a powerful platform for developing applications for semi-structured and unstructured data management.
How to Generate XML-Based Reports A SELECT query usually returns results in the form of rows and columns, which is also known as a rowset. You can optionally retrieve results from a SELECT query in XML format instead of rowset format. To do this, you need to append a FOR XML clause to the SELECT query. The following is the partial syntax of the FOR XML clause.
… FOR XML { RAW | AUTO | EXPLICIT | PATH }… [optional arguments] ::= [,TYPE] [,XMLDATA] [,XMLSCHEMA] [,ROOT(‘RootName’)] [,ELEMENTS] You can use the FOR XML clause in four modes. RAW (element) In this mode, each row in the result set is transformed into an XML element that has a generic identifier |
as the element tag. You can optionally specify a name for the row element that will be used by the resulting XML. If the ELEMENTS directive is added to the FOR XML clause, each column value is mapped to a subelement of the element. You can request the XML schema by specifying the XMLDATA for XDR schema or XMLSCHEMA for XSD schema. The schema appears at the start of the data. In the result, the schema namespace reference is repeated for every toplevel element. AUTO In this mode, query results are returned in a simple, nested XML tree. Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified. You can specify the order of tables identified by the columns in the SELECT clause based on which the XML hierarchy is generated. You can specify the XML schema by using the XMLDATA or XMLSCHEMA option. The first table from the left forms the top element in the resulting XML document. The second table from the left forms a subelement within the top element, and so on. If you specify an already existing column name then it will be added as an attribute of the element. EXPLICIT In this mode, you need to explicitly specify the expected nesting in the XML as part of the query.
You need to write a query to produce the following two metadata columns:
•
The first column must provide the tag number and integer type of the current element, and the column name must be Tag. You must provide a unique tag number for each element that
•
will be constructed from the rowset. The second column must provide a tag number of the parent element, and this column name must be Parent. The Tag and the Parent column provide hierarchy information.
PATH In this mode, you can execute nested FOR XML queries with the TYPE directive to return XML type instances. You can execute most of the EXPLICIT mode queries in the PATH mode. By default, the PATH mode generates a element wrapper for each row in the result set. You can optionally specify an element name which is to be used as the wrapper element name. In the PATH mode, column names or column aliases are treated as XPath expressions. These expressions indicate how the values are being mapped to XML. By default, the PATH mode generates element-centric XML. The following query uses the RAW mode to return rows from the Production.ProductModel table in XML format.
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW The result of rows from the Production.ProductModel table is displayed in XML format. You can optionally specify another name for the element by specifying an optional argument to the RAW mode, by using the following query. The query returns a element for each row in the rowset.
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel') The result of the query returning a element for each row in the rowset is displayed. The above examples return data in an attribute-centric format. To get results in an element-centric format, you need to append the optional ELEMENTS keyword to the FOR XML query.
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel'), ELEMENTS The result in an element-centric format is displayed. To get the schema along with the data, you need to append the XMLSCHEMA keyword to the FOR XML query.
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel'), XMLSCHEMA The schema along with the names of particular ProductModelIDs is displayed.
Using the AUTO Mode The following query uses the AUTO mode to get the results of a SELECT query into XML format.
USE AdventureWorks SELECT * FROM HumanResources.Employee FOR XML AUTO The name of each element as HumanResources.Employee is displayed.
Joining Two Tables The following query joins two tables such as Sales.Customer and Sales.SalesOrderHeader. The CustomerID references the Cust table. Therefore, a element is created and CustomerID is added as its attribute. Next, three columns, OrderHeader.CustomerID, OrderHeader.SaleOrderID, and OrderHeader.Status, reference the OrderHeader table. Therefore, an element is added as a subelement of the element and the three columns are added as attributes of . Next, the Cust.CustomerType column again references the Cust table that was already identified by the Cust.CustomerID column. Therefore, no new element is created. Instead, the CustomerType attribute is added to the element that was previously created. The query specifies aliases for the table names. These aliases appear as corresponding element names. The ORDER BY is required to group all children under one parent.
USE AdventureWorks SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerType FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader WHERE Cust.CustomerID = OrderHeader.CustomerID ORDER BY Cust.CustomerID FOR XML AUTO The element is created first, and then the is added.
Changing the Order of Columns In the following query, you need to change the order of the columns appearing in the SELECT query. Therefore, first the element is created, and then the child element is added to it.
USE AdventureWorks SELECT OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerID, Cust.CustomerType FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader WHERE Cust.CustomerID = OrderHeader.CustomerID FOR XML AUTO The element is created first, and then the element is added.
Using the EXPLICIT Mode
The following example retrieves employee ID and employee names for each employee from the HumanResources.Employee table. Employee names can be obtained from the Contact table. The ContactID column can be used to join the tables. You can use FOR XML EXPLICIT transformation to generate XML as shown in the following XML format.
<Employee EmpID="1" >
USE AdventureWorks SELECT 1 AS Tag, NULL AS Parent, EmployeeID AS [Employee!1!EmpID], NULL AS [Name!2!FName], NULL AS [Name!2!LName] FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID The following is the partial result set of the query.
Tag
Parent
Employee!1!EmpID
Name!2!FName
Name!2!LName
1
NULL
109
NULL
NULL
1
NULL
4
NULL
NULL
1
NULL
9
NULL
NULL
1
NULL
11
NULL
NULL
1
NULL
158
NULL
NULL
The following is the second query. It retrieves values for the element. It assigns 2 as Tag value for the element and 1 as Parent tag value identifying <Employee> as the parent.
USE AdventureWorks SELECT 2 AS Tag, 1 AS Parent, EmployeeID, FirstName, LastName FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID The following is the partial result set of the query.
Tag
Parent
EmployeeID
FirstName
LastName
2
1
15
Jeffrey
Ford
2
1
16
Jo
Brown
2
1
17
Doris
Hartwig
2
1
18
John
Campbell
2
1
19
Diane
Glimp
You can combine the two queries with UNION ALL. You need to apply FOR XML EXPLICIT, and specify the required ORDER BY clause. You need to sort the rowset first by EmployeeID and then by name so that the NULL values in the name appear first. By executing the following query without the FOR XML clause, you can view the universal table generated.
USE AdventureWorks SELECT 1 AS Tag, NULL AS Parent, EmployeeID AS [Employee!1!EmpID], NULL AS [Name!2!FName], NULL AS [Name!2!LName] FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID UNION ALL SELECT 2 AS Tag, 1 AS Parent, EmployeeID, FirstName, LastName FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID ORDER BY [Employee!1!EmpID],[Name!2!FName] FOR XML EXPLICIT The result containing EmpID, FName, and LName are displayed.
Using the PATH Mode The following query uses the PATH mode to generate the ProductModelID column and the name of the product.
USE AdventureWorks SELECT ProductModelID,Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML PATH In the result, the child element names generated are the same as the corresponding column names in the SELECT clause. The following example gives the same result as the query that was written with the EXPLICIT mode. However, the PATH mode is simpler. The aliases specified for each column behave as XPath expressions and allow you to control the nesting of data in the final result set produced.
USE AdventureWorks SELECT emp.EmployeeID AS '@EmpID', Firstname AS 'Name/@FName', Lastname AS 'Name/@LName' FROM HumanResources.Employee emp JOIN Person.Contact c ON emp.ContactID=c.ContactID FOR XML PATH('Employee') The result with nested data is displayed.
Using the Wildcard Character (*) If the column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified. If this column is a non-XML type column, the column content is inserted as a text node, as shown in the following example.
USE AdventureWorks SELECT emp.EmployeeID AS '@EmpID', Firstname AS '*', Lastname AS '*' FROM HumanResources.Employee emp JOIN Person.Contact c ON emp.ContactID=c.ContactID FOR XML PATH('Employee') The result of column content inserted as a text node is displayed.
How to Query XML by Using OpenXML
OPENXML is a T-SQL function that provides a rowset view of the internal representation of XML data. By using OPENXML, you can access XML data as if it were a relational rowset. The records in the rowset can be stored in database tables. OPENXML can only be used in SELECT and SELECT INTO statements wherever rowset providers such as a table, a view, or OPENROWSET are used. The following is the partial syntax of the OPENXML function.
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ]) [ WITH (TableName | SchemaDeclaration ) ] In this syntax:
• • •
idoc is the document handle of the internal representation of an XML document. rowpattern is the XPath pattern used to identify the nodes in the source XML document handle that need to be processed as rows. and TableName or SchemaDeclaration is used with the optional WITH clause to specify the schema in which the rowsets are returned. The following code example shows the usage of OPENXML to query XML data.
How to Use the CONTAINS Predicate Print this page.
The following code example shows the usage of OPENXML to query XML data.
USE AdventureWorks DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc ='
' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider. SELECT * FROM OPENXML (@idoc, '/ROOT/Customer') WITH (CustomerID varchar(10),ContactName varchar(20)) EXEC sp_xml_removedocument @idoc In the preceding query, first, a varchar variable is created and assigned some XML content. Next, an internal representation of the XML image is created by using sp_xml_preparedocument. This stored procedure returns a document handle, which is stored in the integer variable @doc. Then, a SELECT statement with OpenXML is executed against the internal representation of the XML document. The XPath function ‘/ROOT/Customer’ returns the two customer nodes. OpenXML creates a two-column rowset (CustomerID and ContactName) from which the SELECT statement retrieves the necessary columns. The following is the result set of the query. CustomerIDContactNameVINETVictor
What Is XQuery?
IsakovLILASMarion Siekierski
XQuery is a declarative, typed, functional language designed for the purpose of querying data stored in structured or semi-structured format. You can use XQuery to work with XML documents that are untyped with no associated schema, documents that are typed with associated XML schemas, or a combination of both. By using the extensions for XQuery in SQL Server 2005, not only can you query XML data, but you can also modify it. XQuery is a highly efficient method to query huge chunks of data. It also provides the capability to filter, sort, order, and repurpose the required information. Need for XQuery The XQuery language was implemented in SQL Server for the following reasons:
• •
By using the XML data type, you can store typed or untyped XML data in database tables. The SQL SELECT implementation in SQL Server 2005 cannot understand XML data. Therefore, an alternative language that can understand and work with XML data had to be introduced in
•
SQL Server 2005. DML statements such as INSERT, UPDATE, and DELETE work with all the data stored in a column. When working with XML data stored in a column, you may want to manipulate or modify just certain nodes or elements instead of the entire chunk of data. By using the enhancements for the XQuery language in SQL Server 2005, you can extend DML statements to transform certain nodes or elements in the underlying XML data.
Key features of XQuery The FOR, LET, WHERE, ORDER BY, and RETURN clauses form the core expressions of XQuery and are similar to the SELECT statements of SQL.
•
The FOR clause enables you to define a declarative iteration of a bound variable used over an input sequence. It is equivalent to the SQL SELECT FROM clause.
•
The LET clause is used for binding a variable to the results of an expression. However, the LET clause is not supported by the XQuery implementation in SQL Server 2005. You can use an
•
inline expression instead. The WHERE clause filters the results of an iteration by applying the expression specified with
•
the WHERE clause. The ORDER BY clause enables you to sort the values in the returned result set. The ORDER BY
•
clause accepts a sorting expression which returns an atomic value. The RETURN clause, which is analogous to the SELECT clause in SQL, enables you to define the result of a query. You can specify any valid XQuery expression in the RETURN clause. You can also construct XML structures in the RETURN section by specifying constructors for elements and attributes.
How to Query XML by Using Xquery You can use various methods provided in the XQuery language to query and modify XML data in SQL Server. The different XQuery methods are: query(), value(), exist(), modify(), and nodes(). query() The query() method specifies an XQuery expression to be executed against an instance of XML data. This method returns an instance of untyped XML. The following code example shows a variable @myDoc of XML type and assigns an XML instance to it. Then a query() method is used to specify an XQuery against the document. The query then retrieves the <Warranty> element of the element.
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available ' SELECT @myDoc.query('/Root/ProductDescription/Features/W arranty') The following code example shows how you can rewrite the same query to use a FLWOR expression instead of a XPATH expression.
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available 'SELECT @myDoc.query(' for $d in /Root/ProductDescription/Features/Warranty return $d ')
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available ' SELECT @myDoc.query('/Root/ProductDescription/Features/W arranty') The following code example shows how you can rewrite the same query to use a FLWOR expression instead of a XPATH expression.
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available 'SELECT @myDoc.query(' for $d in /Root/ProductDescription/Features/Warranty return $d ')
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available ' SELECT @myDoc.query('/Root/ProductDescription/Features/W arranty') The following code example shows how you can rewrite the same query to use a FLWOR expression instead of a XPATH expression.
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available 'SELECT @myDoc.query(' for $d in /Root/ProductDescription/Features/Warranty return $d ')
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available ' SELECT @myDoc.query('/Root/ProductDescription/Features/W arranty') The following code example shows how you can rewrite the same query to use a FLWOR expression instead of a XPATH expression.
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = ' <Warranty>1 year parts and labor <Maintenance>3 year parts and labor extended maintenance is available 'SELECT @myDoc.query(' for $d in /Root/ProductDescription/Features/Warranty return $d ')
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
Data Category
Structured data is organized in semantic groups or entities. Similar entities are grouped together by using relations or classes. Entities in the same group have the same descriptions or attributes. Also, descriptions can be provided for all entities in a group by using a schema. Structured data is typically stored in a Relational Database Management System (RDBMS) and is also referred to as relational data. The data is stored in the form of rows and columns. The columns allow you to constrain the type and the amount of data that can be stored. Structured data provides you various advantages. You can normalize your data into multiple tables, thereby reducing redundancy. You can also create efficient data structures to minimize data size. Besides this, it is possible to control data elements by using various constraints and enforce business rules for data integrity. However, structured data has a few disadvantages as well. It is difficult to change the structure of the data when your application changes drastically. Data with many variable input parameters can result in the creation of many columns holding just NULL values. Because the data is stored in a proprietary binary format, it cannot interact directly with other external systems. Semi-structured data is data that is available electronically in file systems or data exchange formats such as bibliographic data or scientific data. Semi-structured data is often organized in semantic entities where similar entities are grouped together. However, entities in the same group may not have the same attributes. XML is a widely used form of semi-structured data. Semi-structured data is suitable for scenarios where the structure of the data is not known completely or is likely to change significantly in future. Data can be retrieved in exactly the same order that it was stored. Because XML is an open standard, the data can be used by multiple systems running on disparate platforms without any changes. Security can be implemented by assigning several levels of permissions for different users. XML is also useful for data interchange among loosely coupled, disparate systems, such as B2B applications and workflow applications. XML is a suitable form of data storage in document management applications, where most documents such as e-mail messages and reports are semi-structured. Using XML has a few disadvantages as well. Every unit of data has to be marked up with tags and this increases storage space. This also increases the amount of network bandwidth required to transfer a unit of data from one system to another. Because XML data is represented in a text-based format, the data may be tampered with. Also, constraints are more relaxed. Unstructured data could be of any type. It may not comply with standards and is not predictable. Examples of unstructured data could include text, videos, sound, images, e-
mail messages, memos, user groups, chats, reports, letters, surveys, white papers, marketing materials, research, presentations and Web pages. Unstructured data provide a few advantages. The format of the data can be changed at any time without any major effort. The data can be represented in various ways to suit application requirements. Also, you can store multiple formats of data in the same document. Unstructured data has many disadvantages. You cannot easily define the various elements of the data because it is generally stored as a series of values delimited by markers, for example, comma separated values (CSVs). Security cannot be defined on text data. Because you cannot have an indexing mechanism, performance can be slow if the size of the data is very large. What Is Metadata? Metadata is defined as data about data. The users access metadata to view additional and technical information. Metadata views provide an internal, system table-independent view of the SQL Server metadata, and contain all data objects stored in that particular database. Metadata consists of two categories namely, business-oriented metadata and technical metadata.
Metadata Categories Metadata is generally used to provide users with additional information about the data that they access or to hide the complexity of the technical details of the data that they view. It can also be used by systems to determine data types, the time the data was last changed, and what kind of changes were made to the data. Metadata can be used by applications and systems to perform type checking, data validation and auto formatting of data. Metadata consists of two categories: businessoriented metadata, and technical metadata. Business-oriented metadata includes metadata that a user can access such as the table names, column names, column descriptions, formatting rules, or business definitions of data elements. By contrast, technical metadata contains information that supports the back office functions and the other built-in capabilities offered by SQL Server 2005 through components such as SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services. Metadata Views In SQL Server 2005, metadata is used throughout the platform products, starting with the database engine to other servers and applications. Each server exposes metadata programmatically, as well as through the functionalities included in the design and administrative tools. In SQL Server 2005, metadata is stored in hidden resource tables, which can be accessed directly by the server. You can use the new catalog views or Dynamic Management Views (DMVs), which show data obtained from both the hidden resource tables and the various system functions. The system tables can be implemented by using compatibility views for providing backward compatibility with earlier versions of SQL Server. Besides this, there are more than 200 catalog views, INFORMATION_SCHEMA views, and management views in SQL Server 2005. These views provide simple details such as the database structure for complex information like the amount of processor time a query requires to be executed.
How to Query Metadata by Using SQL Server Views SQL Server exposes metadata by using a number of relational objects like INFORMATION_SCHEMA views, Dynamic Management Views, and catalog views. By using these SQL Server views, you can query metadata. Dynamic management Views and catalog views are new features introduced in SQL Server 2005. INFORMATION_SCHEMA views, in SQL Server 2005, contain the metadata of all the data objects in the database. Information schema views provide an internal, system-table independent view of SQL Server metadata. This permits the applications to work correctly though significant changes have been made to the system tables in SQL Server 2005. However, INFORMATION_SCHEMA views provide limited information. If you require detailed information, you can use catalog views. The information schema views included in SQL Server conform to the ANSI standard as well as the SQL-92 Standard definition for INFORMATION_SCHEMA.
Dynamic Management Views (DMVs) return server state information that can be used to monitor the server, check for problems, and to facilitate performance. DMVs return specific data that can change in the future releases of SQL Server. The two types of DMVs are server-scoped DMVs and database-scoped DMVs. All DMVs and functions exist in the sys schema and follow the naming convention dm_*. You should prefix the name of the view or function by using the sys schema.
Catalog views return information that is used by the SQL Server 2005 database engine. You can use catalog views to query metadata because they transform, and present customized forms of information efficiently. All user-available catalog metadata are exposed through catalog views. Some catalog views inherit rows from other catalog views. For instance, the tables catalog view inherits the objects catalog view. This means that the tables catalog view has the columns that are specific to tables, and also all the columns that belong to the objects catalog view. All catalog views are present in a system-level schema named SYS.
How to Query Metadata by Using SQL Server Commands You can use system functions, Database Console Commands (DBCC), and system stored procedures to access metadata information. System functions perform operations on and return metadata information about the values, objects and settings in SQL Server 2005. The following are some of the system functions.
• • • • • • •
SYSTEM_USER SERVERPROPERTY OBJECTPROPERTY CURRENT_TIMESTAMP CURRENT_USER DATALENGTH ISNUMERIC
The SYSTEM_USER function can be used to return the name of the currently executing context. The following example declares a CHAR variable, stores the current value of SYSTEM_USER in the variable and then prints the value stored in the variable.
USE AdventureWorks DECLARE @sys_usr char(30); SET @sys_usr = SYSTEM_USER; SELECT 'The current system user is: '+ @sys_usr; The following example uses the SERVERPROPERTY function to get the server name and to verify whether the server uses only Integrated Windows Authentication.
USE AdventureWorks SELECT SERVERPROPERTY('servername'), SERVERPROPERTY('IsIntegratedSecurityOnly')
DBCC commands can be used not just for returning various types of information, but also for performing maintenance tasks on a database, index or filegroup, validation operations on databases or tables, and also for miscellaneous tasks such as enabling trace flags or removing DLLs from memory. The following are some of the DBCC commands.
• • • • • • • •
DBCC OPENTRAN DBCC USEROPTIONS DBCC CONCURRENCYVIOLATION DBCC SHOW_STATISTICS DBCC CHECKALLOC DBCC CHECKFILEGROUP DBCC SHOWCONTIG DBCC HELP The following example is a DBCC command that returns all the SET options that are active for the current connection.
USE AdventureWorks DBCC USEROPTIONS The following command displays fragmentation information for the HumanResources.Employee table.
USE AdventureWorks DBCC SHOWCONTIG ('HumanResources.Employee')
System stored procedures, in SQL Server 2005, can be used to perform administrative and informational activities. The following are some of the system stored procedures.
• •
sp_column_privileges sp_special_columns
• • • • • • •
sp_cursor_list sp_add_maintenance_plan sp_catalogs sp_send_dbmail sp_fulltext_database sp_spaceused sp_help The following example returns column information for a specific column by using the sp_columns.
USE AdventureWorks GO EXEC sp_columns @table_name = N'Department', @table_owner = N'HumanResources' The following example disables full-text indexing for the AdventureWorks database.
USE AdventureWorks GO EXEC sp_spaceused N'Production.product'; GO
Lesson Introduction The Extensible Markup Language (XML) is a general-purpose markup language recommended by the World Wide Web Consortium (W3C), for creating special-purpose markup languages, capable of describing various kinds of data. XML is a simplified subset of Standard Generalized Markup Language (SGML). The data may be in the form of a record from a data store or an entire document. When you store XML data in a relational database, it provides you the benefits of better data management and query processing. SQL Server provides powerful capabilities to query and modify XML data.
What Is XML? XML provides a text-based means to describe and apply a tree-based structure to information. XML has the ability to represent most data structures such as records, lists and trees. The strict syntax and parsing requirements of XML allow the necessary parsing algorithms to help it remain simple, efficient, and consistent. The XML format is robust, logically-verifiable, and based on international standards. The hierarchical structure is suitable for most types of documents. Interoperability of the data defined in XML format is also another of its benefits. The greatest power of XML is its extensibility. You can invent your own tags with your own semantic meaning. For example, you can create a tag to store customer information data. Your data stream may contain multiple customers and customer information. If you want to find all customers with a specific name, you can do that by searching for the Customer_First_Name tags. An XML document contains various elements. The first line is the XML declaration, an optional line stating what version of XML is in use. An element usually consists of a start tag and a closing tag pair. Between the start tag and closing tag pair, an element may contain data, content, or other elements. An element can contain just the closing tag. The first element that the XML processor encounters must consist of a start tag and a closing tag and is called the root element. All other elements will be within the root element and are called child elements.
A child element can contain child elements of its own. An element can contain attributes. You can use attributes as an alternative to using elements to store data. You can create an attribute in the start tag of an element. You can declare the name of the attribute, followed by a value assignment. You can use either single or double quotation marks to set the value of an attribute. Comments are optional. These are some of the basic rules.
Scenarios Where XML Is Used SQL Server 2005 provides powerful storage, query, and data modification capabilities over XML data. XML data can interoperate with existing relational data and SQL applications. Therefore, XML can be introduced into the system as data modeling needs increase without disrupting your existing applications. Besides these requirements, XML can be useful in many scenarios. Data Exchange XML is a platform-independent format for data representation. So it is useful for exchanging information among loosely-coupled, disparate systems, such as in business-to-business (B2B) applications and workflow situations. Data interchange has been a major advantage of XML technologies. Document Management XML is being increasingly used in enterprise applications for modeling semi-structured and unstructured data. Documents, such as e-mail, are semi-structured in nature. If documents are stored inside a database server in XML format, then powerful applications can be developed that retrieve documents based on document content, query for partial content, and perform document aggregation. Such scenarios are becoming feasible with the rise in applications that generate and consume XML. Messaging XML can be used in a messaging environment, to enable applications to exchange messages with other applications. In Enterprise Application Integration scenarios, when various applications need to be integrated, XML is the format used to interchange data. Mid-tier Collaboration XML can be used for exchanging data between the middle tier and the other tiers in an application. Traditionally the presentation tier sent data to the middle tier in a proprietary binary format. This restricted the kind of technology that could be used to implement the various tiers. Also external applications running on disparate systems could not communicate with a common middle tier component. XML solved these problems by introducing a common standard for representing data. XML Web Services can be effectively used as a middle-tier component that receives and sends all data in XML format. Ad-hoc Modeling XML can be used for ad-hoc modeling of the data. If the structure of the data changes or evolves, the changes can be incorporated in the XML document. Standards such as XPath, XQuery, and XSLT make it very easy to process the data and render it in any other suitable format.
How SQL Server Implements XML SQL Server 2005 provides extensive support for XML data processing. XML values can be stored natively in an XML data type column, which can be typed according to a collection of XML schemas, or left untyped. You can index the XML column. Furthermore, fine-grained data manipulation is supported using XQuery and XML DML, the latter being an extension for data modification. SQL Server implements XML by passing the incoming XML through various processes. First step As the first step in working with XML, SQL Server feeds the incoming XML to an XML Parser. The XML Parser interprets the XML inputs and presents them in the form of a known interface such as a Document Object Model (DOM). XML Parsers also verify that the XML document is wellformed and is syntactically and grammatically valid. XML Parsers can also be used as validating parsers to validate the XML data against a schema. Second step When you provide a schema for the XML documents you will store, you can specify the name of the schema collection that contains the schema that you want to apply to that column. Then,
SQL Server feeds the schema to the XML Parser, which validates the incoming XML against the schema. After this, the validated XML is implicitly converted to the XML data type. Then, the XQuery methods available in SQL Server 2005 such as query(), value(), nodes() or modify() are applied on the converted XML data. Third step The converted XML data can be used for various purposes. You can use the OPENXML nodes method to convert XML data into a rowset format. However, before the XML data is converted to rowsets, SQL Server builds a node table internally. You can apply XQuery methods such as query or value on the contents of the node table. Fourth step To speed up the processing of the data, SQL Server makes use of a primary XML index that can be defined on the column containing XML data. The primary XML index indexes all the tags, values, and paths over the XML instances in the column and benefits query performance. By using the primary XML index, three types of secondary XML indexes - the Path Index, the Property Index and the Value Index – can be created on the XML column to speed up queries. Depending on the scenario and the nature of the XML data, SQL Server uses any of these secondary indexes. Fifth step With these features that provide XML support, SQL Server 2005 provides a powerful platform for developing applications for semi-structured and unstructured data management.
How to Generate XML-Based Reports A SELECT query usually returns results in the form of rows and columns, which is also known as a rowset. You can optionally retrieve results from a SELECT query in XML format instead of rowset format. To do this, you need to append a FOR XML clause to the SELECT query. The following is the partial syntax of the FOR XML clause.
… FOR XML { RAW | AUTO | EXPLICIT | PATH }… [optional arguments]
You need to write a query to produce the following two metadata columns:
•
The first column must provide the tag number and integer type of the current element, and the column name must be Tag. You must provide a unique tag number for each element that
•
will be constructed from the rowset. The second column must provide a tag number of the parent element, and this column name must be Parent. The Tag and the Parent column provide hierarchy information.
PATH In this mode, you can execute nested FOR XML queries with the TYPE directive to return XML type instances. You can execute most of the EXPLICIT mode queries in the PATH mode. By default, the PATH mode generates a
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW The result of rows from the Production.ProductModel table is displayed in XML format. You can optionally specify another name for the
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel') The result of the query returning a
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel'), ELEMENTS The result in an element-centric format is displayed. To get the schema along with the data, you need to append the XMLSCHEMA keyword to the FOR XML query.
USE AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119 FOR XML RAW('ProductModel'), XMLSCHEMA The schema along with the names of particular ProductModelIDs is displayed.
Using the AUTO Mode The following query uses the AUTO mode to get the results of a SELECT query into XML format.
USE AdventureWorks SELECT * FROM HumanResources.Employee FOR XML AUTO The name of each element as HumanResources.Employee is displayed.
Joining Two Tables The following query joins two tables such as Sales.Customer and Sales.SalesOrderHeader. The CustomerID references the Cust table. Therefore, a
USE AdventureWorks SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerType FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader WHERE Cust.CustomerID = OrderHeader.CustomerID ORDER BY Cust.CustomerID FOR XML AUTO The
Changing the Order of Columns In the following query, you need to change the order of the columns appearing in the SELECT query. Therefore, first the
USE AdventureWorks SELECT OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerID, Cust.CustomerType FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader WHERE Cust.CustomerID = OrderHeader.CustomerID FOR XML AUTO The
Using the EXPLICIT Mode
The following example retrieves employee ID and employee names for each employee from the HumanResources.Employee table. Employee names can be obtained from the Contact table. The ContactID column can be used to join the tables. You can use FOR XML EXPLICIT transformation to generate XML as shown in the following XML format.
<Employee EmpID="1" >
USE AdventureWorks SELECT 1 AS Tag, NULL AS Parent, EmployeeID AS [Employee!1!EmpID], NULL AS [Name!2!FName], NULL AS [Name!2!LName] FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID The following is the partial result set of the query.
Tag
Parent
Employee!1!EmpID
Name!2!FName
Name!2!LName
1
NULL
109
NULL
NULL
1
NULL
4
NULL
NULL
1
NULL
9
NULL
NULL
1
NULL
11
NULL
NULL
1
NULL
158
NULL
NULL
The following is the second query. It retrieves values for the
USE AdventureWorks SELECT 2 AS Tag, 1 AS Parent, EmployeeID, FirstName, LastName FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID The following is the partial result set of the query.
Tag
Parent
EmployeeID
FirstName
LastName
2
1
15
Jeffrey
Ford
2
1
16
Jo
Brown
2
1
17
Doris
Hartwig
2
1
18
John
Campbell
2
1
19
Diane
Glimp
You can combine the two queries with UNION ALL. You need to apply FOR XML EXPLICIT, and specify the required ORDER BY clause. You need to sort the rowset first by EmployeeID and then by name so that the NULL values in the name appear first. By executing the following query without the FOR XML clause, you can view the universal table generated.
USE AdventureWorks SELECT 1 AS Tag, NULL AS Parent, EmployeeID AS [Employee!1!EmpID], NULL AS [Name!2!FName], NULL AS [Name!2!LName] FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID UNION ALL SELECT 2 AS Tag, 1 AS Parent, EmployeeID, FirstName, LastName FROM HumanResources.Employee E, Person.Contact C WHERE E.ContactID = C.ContactID ORDER BY [Employee!1!EmpID],[Name!2!FName] FOR XML EXPLICIT The result containing EmpID, FName, and LName are displayed.
Using the PATH Mode The following query uses the PATH mode to generate the ProductModelID column and the name of the product.
USE AdventureWorks SELECT ProductModelID,Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML PATH In the result, the child element names generated are the same as the corresponding column names in the SELECT clause. The following example gives the same result as the query that was written with the EXPLICIT mode. However, the PATH mode is simpler. The aliases specified for each column behave as XPath expressions and allow you to control the nesting of data in the final result set produced.
USE AdventureWorks SELECT emp.EmployeeID AS '@EmpID', Firstname AS 'Name/@FName', Lastname AS 'Name/@LName' FROM HumanResources.Employee emp JOIN Person.Contact c ON emp.ContactID=c.ContactID FOR XML PATH('Employee') The result with nested data is displayed.
Using the Wildcard Character (*) If the column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified. If this column is a non-XML type column, the column content is inserted as a text node, as shown in the following example.
USE AdventureWorks SELECT emp.EmployeeID AS '@EmpID', Firstname AS '*', Lastname AS '*' FROM HumanResources.Employee emp JOIN Person.Contact c ON emp.ContactID=c.ContactID FOR XML PATH('Employee') The result of column content inserted as a text node is displayed.
How to Query XML by Using OpenXML
OPENXML is a T-SQL function that provides a rowset view of the internal representation of XML data. By using OPENXML, you can access XML data as if it were a relational rowset. The records in the rowset can be stored in database tables. OPENXML can only be used in SELECT and SELECT INTO statements wherever rowset providers such as a table, a view, or OPENROWSET are used. The following is the partial syntax of the OPENXML function.
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ]) [ WITH (TableName | SchemaDeclaration ) ] In this syntax:
• • •
idoc is the document handle of the internal representation of an XML document. rowpattern is the XPath pattern used to identify the nodes in the source XML document handle that need to be processed as rows. and TableName or SchemaDeclaration is used with the optional WITH clause to specify the schema in which the rowsets are returned. The following code example shows the usage of OPENXML to query XML data.
How to Use the CONTAINS Predicate Print this page.
The following code example shows the usage of OPENXML to query XML data.
USE AdventureWorks DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc ='
What Is XQuery?
IsakovLILASMarion Siekierski
XQuery is a declarative, typed, functional language designed for the purpose of querying data stored in structured or semi-structured format. You can use XQuery to work with XML documents that are untyped with no associated schema, documents that are typed with associated XML schemas, or a combination of both. By using the extensions for XQuery in SQL Server 2005, not only can you query XML data, but you can also modify it. XQuery is a highly efficient method to query huge chunks of data. It also provides the capability to filter, sort, order, and repurpose the required information. Need for XQuery The XQuery language was implemented in SQL Server for the following reasons:
• •
By using the XML data type, you can store typed or untyped XML data in database tables. The SQL SELECT implementation in SQL Server 2005 cannot understand XML data. Therefore, an alternative language that can understand and work with XML data had to be introduced in
•
SQL Server 2005. DML statements such as INSERT, UPDATE, and DELETE work with all the data stored in a column. When working with XML data stored in a column, you may want to manipulate or modify just certain nodes or elements instead of the entire chunk of data. By using the enhancements for the XQuery language in SQL Server 2005, you can extend DML statements to transform certain nodes or elements in the underlying XML data.
Key features of XQuery The FOR, LET, WHERE, ORDER BY, and RETURN clauses form the core expressions of XQuery and are similar to the SELECT statements of SQL.
•
The FOR clause enables you to define a declarative iteration of a bound variable used over an input sequence. It is equivalent to the SQL SELECT FROM clause.
•
The LET clause is used for binding a variable to the results of an expression. However, the LET clause is not supported by the XQuery implementation in SQL Server 2005. You can use an
•
inline expression instead. The WHERE clause filters the results of an iteration by applying the expression specified with
•
the WHERE clause. The ORDER BY clause enables you to sort the values in the returned result set. The ORDER BY
•
clause accepts a sorting expression which returns an atomic value. The RETURN clause, which is analogous to the SELECT clause in SQL, enables you to define the result of a query. You can specify any valid XQuery expression in the RETURN clause. You can also construct XML structures in the RETURN section by specifying constructors for elements and attributes.
How to Query XML by Using Xquery You can use various methods provided in the XQuery language to query and modify XML data in SQL Server. The different XQuery methods are: query(), value(), exist(), modify(), and nodes(). query() The query() method specifies an XQuery expression to be executed against an instance of XML data. This method returns an instance of untyped XML. The following code example shows a variable @myDoc of XML type and assigns an XML instance to it. Then a query() method is used to specify an XQuery against the document. The query then retrieves the <Warranty> element of the
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
USE AdventureWorks DECLARE @myDoc XML SET @myDoc = '
value() The value() method performs an XQuery against the XML data and returns a scalar SQL type. This method is used to extract a value from an XML instance stored in an XML data type column, or variable. You can use an XML namespace, which is a collection of names, identified by a Uniform Resource Identifier (URI) reference, which are used in XML documents as element and attribute names. You can also specify SELECT queries that combine or compare XML data with non-XML columns. The following statement shows how the value() method is typically used.
value (XQuery, SQLType) In this syntax, XQuery is the XQuery expression that retrieves data from XML instances. SQLType is the preferred SQL scalar type to be returned. The following code example shows how to rewrite the previous query to use the value() method. The value method retrieves the value in the ProductModelName attribute and then converts it to the nvarchar data type.
WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription' AS PD, 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm) SELECT CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelName', 'nvarchar(50)') AS
