Chapter 7 Querying and Managing XML Data

Question 1

What is XQuery?

Answer 1

It’s short for XML Query Language. It’s the standard language used to query and manipulate XML data.

Question 2

What are elements?

Answer 2

Elements are part of an XML document. They are made up of a begin tag and an end tag. Elements can be nested.

Question 3

What are tags?

Answer 3

Tags are used to name parts of an XML document, e.g. . Tags cannot be interleaved.

Question 4

What is an XML document?

Answer 4

TODO

Question 5

What is a well-formed XML document?

Answer 5

An XML document is said to be well formed if every begin tag has a corresponding end tag and if tags are nested properly.

Question 6

Are XML documents “ordered”?

Answer 6

Yes, but not by any specific element value; it means the position of elements matters.

Question 7

Is XML case-sensitive?

Answer 7

Yes. XML is case-sensitive unicode text.

Question 8

What is markup?

Answer 8

Characters that have special meaning such as ‘ which introduces a tag.

Question 9

What do you do if you want to include markup in the values of your XML?

Answer 9

They must be escaped by using an ampersand (&), a special code, followed by a semicolon (;). For example:
& => &
" => "
< => <
> => >
' => &apos;

Question 10

What is XML CDATA?

Answer 10

CDATA is short for character data. This will prevent special characters in the string from being parsed as XML makup. It’s written as <![CDATA[…]]> The … can be replaced with any character string.

Question 11

What is the prolog of an XML document?

Answer 11

The prolog occurs at the beginning of the document and denotes the XML version and encoding of the document, such as

Question 12

What is an XML fragment and how is it different from an XML document?

Answer 12

An XML document has a single root node. A fragment does not have a root node.

Question 13

What are attributes?

Answer 13

Attributes are name value pairs that are listed on elements. They have their own names and their values are enclosed in quotation marks.

Question 14

What is an attribute centric presentation?

Answer 14

Where each “property” is written as an attribute on the elements.

Question 15

What is an element centric presentation?

Answer 15

Where each “property” is written as a nested element of the original element.

Question 16

Do element names need to be unique?

Answer 16

No, element names do not have to be unique because they can be referred to by their position.

Question 17

What are namespaces?

Answer 17

Namespaces can be used to distinguish elements that share the same name. You declare namespaces that are used in the root element of an XML document. You can also add an alias for each namespace. The namespace alias is used to prefix element names, e.g.

Question 18

What is a schema?

Answer 18

Describes the metadata of XML documents. Currently, the most widely used metadata description is with XML Schema Description (XSD) documents. XSD documents are XML documents that describe the metadata of other XML documents. It includes element names, data types, number of occurrences of an element, constraints, and more.

Question 19

What is a typed XML document?

Answer 19

A document with a predefined schema. A document that complies with or has been validated against a schema.

Question 20

How do you produce XML from relational data?

Answer 20

Use SELECT … FROM … FOR XML construct.

Question 21

What does the FOR XML RAW option do?

Answer 21

In RAW mode, every row from returned rowsets converts to a single element named row and columns translate to the attributes of this element, e.g. This behavior can be enhanced to include a root element, namespaces, and making the XML element-centric, e.g. FOR XML RAW, ROOT(‘ProductModel’), ELEMENTS.

Question 22

What does the FOR XML AUTO option do?

Answer 22

AUTO mode generates nested elements in the resulting XML using heuristics based on the way the SELECT statement is specified, e.g. JOINS, etc. You have minimal control over the shape of the XML generated.

Question 23

In AUTO and RAW modes, what does the ELEMENTS KEYWORD do for you?

Answer 23

Produces element-centric XML, e.g. FOR XML AUTO, ELEMENTS

Question 24

What does the WITH NAMESPACES clause do?

Answer 24

The WITH NAMESPACES clause precedes the SELECT part of the query and defines the namespaces and aliases in the returned XML, e.g. WITH XMLNAMESPACES(‘TK461-CustomersOrders’ AS co)… SELECT

Question 25

How are the table and column aliases in the query used to produce element names?

Answer 25

You can alias column and table names such as SELECT [co:Customer].custid as [co:custid]
FROM Sales.Customers AS [co:Customer]
using a colon to separate the namespace from the element and this will in turn produce the element names.

Question 26

Why is ORDER BY important when using T-SQL SELECT to format and return XML?

Answer 26

Without the ORDER BY clause, the order of rows returned is unpredictable and you can get a weird XML document with an element repeated multiple times with just part of the nested elements every time.

Question 27

Where does the FOR XML clause go in relation to the ORDER BY clause?

Answer 27

The FOR XML clause comes after the ORDER BY clause in a query.

Question 28

Why is the order of columns important when using T-SQL SELECT to format and return XML?

Answer 28

SQL Server uses column order to determine the nesting of elements. The order of the columns should follow one-to-many relationships. A customer can have many orders; therefore, you should have the customer columns before the order columns in your query.

Question 29

What does the XMLSCHEMA option do?

Answer 29

FOR XML in both RAW and AUTO modes can also return the XSD schema of the document you are creating. This schema is included inside the XML that is returned before the actual data - i.e. it is called inline schema. The XMLSCHEMA directive accepts a parameter where you define a target namespace, e.g. FOR XML AUTO, ELEMENTS, XMLSCHEMA(‘TK461-CustomersOrders’);

Question 30

How can you use the XMLSCHEMA option to only export the schema and not include any data?

Answer 30

Simply include a WHERE condition in your query with a predicate that no row can satisfy, e.g. WHERE 1 = 2 FOR XML AUTO, ELEMENTS, XMLSCHEMA(‘TK461-CustomersOrders’);

Question 31

What does the FOR XML EXPLICIT option do?

Answer 31

The EXPLICIT mode allows you to manually define the XML returned - you have total control. It is included for backwards compatibility only; it uses proprietary T-SQL syntax for formatting XML.

Question 32

What does the FOR XML PATH option do?

Answer 32

The PATH mode allows you to manually define the XML returned - you have total control. It uses standard XML XPath expression to define the elements and attributes of the XML you are creating. By default, every column becomes an element; if you want to generate attribute-centric XML, prefix the alias name with the at (@) character.

SELECT Customer.custid AS [@custid],
Customer.companyname as [companyname]
FROM Sales.Customers AS Customer
ORDER BY Customer.custid
FOR XML PATH ('Customer'), ROOT('Customers')

Question 33

What is an XPath expression?

Answer 33

An XPath Expression define the path to an element in XML. The path is expressed in a hierarchical way; levels are delimited with the slash character (/).

Question 34

How can you create XML with nested elements for child tables?

Answer 34

Using FOR XML PATH mode, you have to use subqueries in the SELECT part of the query. Subsqueries have to return a scalar value. However, the scalar value can be a single scalar XML value also formed by using FOR XML. Additionally, you have to use the TYPE directive of the FOR XML clause to produce a value of the XML data type and not XML as text which cannot be consumed by the outer query.

Question 35

How do you convert XML data to relational data?

Answer 35

This is called “shredding xml” and there are two ways: (1) using the nodes method of the XML data type, but also (starting with SQL Server 2000) (2) using the OPENXML rowset function.

Question 36

What is the OPENXML function?

Answer 36

The OPENXML function provides a rowset over in-memory XML documents using DOM presentation. The function uses 4 parameters:

(1) An XML DOM handle (an integer), returned by sp_xml_preparedocument
(2) An XPath expression which defines how XML nodes translate to rows.
(3) A description of the rowset returned (optional) called flags: 1 means attribute centric, 2 means element centric, and 3 means both, but is not a best practice to use it. Flag value 8 can be bitwise combined with 1 and 2 (1 OR 2 OR 8 = 11) to get both attribute and element-centric mappings.
(4) Mapping between XML nodes and rowset columns. You can map XML elements or attributes to rows and columns by using the WITH clause. In this clause, you can specify an existing table which is used as a template for the rowset returned, or you can define a table with syntax similar to that in the CREATE TABLE statement.

Question 37

What must be done before and after you use OPENXML to shred an XML document?

Answer 37

Before parsing the DOM, you need to prepare it using a system stored procedure: sp_xml_preparedocument. After you shred the document, you must remove the DOM presentation by using the system stored procedure sys.sp_xml_removedocument.

Question 38

What is an example of using all steps of OPENXML?

Answer 38

DECLARE @DocHandle AS INT;
DECLARE @XmlDocument AS VARCHAR(1000);

EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

SELECT * FROM OPENXML(@DocHandle, ‘/CustomersOrders/Customer’, 1) WITH (custid INT, companyname VARCHAR(50));

EXEC sys.sp_xml_removedocument @DocHandle;

Question 39

What’s the difference between the nodes and OPENXML methods for shredding XML documents?

Answer 39

The nodes method of the XML data type is more efficient for shredding an XML document only once and is therefore the preferred way of shredding in such as case. However, if you need to shred the same document multiple times, then preparing the document once, using OPENXML multiple times and removing the DOM presentation might be faster.

Question 40

Is XQuery case-sensitive?

Answer 40

Yes. Like XML, XQuery is case-sensitive.

Question 41

What does XQuery return?

Answer 41

XQuery returns sequences. Sequences can include atomic values or complex values (XML nodes). Any node such as an element, attribute, text, processing instruction, comment or document can be included. The sequence can be formatted to get well-formed XML.

Question 42

What is a QName?

Answer 42

Every identifier in XQuery is a QName, or “qualified name”. A QName consists of a local name and optionally a namespace prefix, e.g. root, a, b, c, or d are examples of QNames without a namespace prefix.

Question 43

What standard namespaces are defined in SQL Server?

Answer 43

(1) xs - The namespace for an XML schema, (2) xsi - The schema instance namespace used to associate XML schemas with instance documents, (3) xdt - The namespace for XPath and XQuery datatypes, (4) fn - The functions namespace, (5) sqltypes - The namespace that provides mapping for SQL Server data types, (6) xml - The default XML namespace.
* You can use these namespaces in your queries without defining them again.

Question 44

What are the 3 ways you can define your own data types in XQuery?

Answer 44

(1) In the prolog which belongs at the beginning of your XQuery. You separate the prolog from the query body using a semicolon. (2) You can also declare namespaces used in XQuery expressions in advance in the WITH clause of the T-SQL SELECT command. (3) If your XML uses a single namespace, you can also declare it as the default namespace for all elements in the XQuery prolog.

Question 45

What is the syntax for XQuery comments?

Answer 45

Comments can also be included in you XQuery expressions. The syntax for a comment is: (: this is a comment :). This is not an XML comment in that it has no influence on the XML returned.

Question 46

What are some examples of the 3 ways you can define your own data types in XQuery?

Answer 46

(1) Namespace in prolog:
SELECT @x.query(‘
declare namespace co=”TK461-CustomersOrders”;
//co:Customer[1]/*) AS [Prolog]

(2) Default Namespace:
SELECT @x.query(‘
declare default element namespace “TK4610CustomersOrders”;
//Customer[1]/*’) AS [Default]

(3) Namespace defined in WITH:
WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)
SELECT @x.query('
//co:Customer[1]/*') AS [With]

Question 47

What happens when you use the default element namespace?

Answer 47

If you use the default element namespace, the namespace is not included for the elements in the resulting XML; it is included for attributes. In addition, when you use the default element namespace, you can’t define your own namespace abbreviation. You should prefer an explicit option to use the default.

Question 48

What types make up the “node types” in the list of XQuery data types?

Answer 48

The node types include (1) attribute, (2) comment, (3) element, (4) namespace, (5) text, (6) processing instruction, (7) document node.

Question 49

What types make up the “atomic types” in the list of XQuery data types?

Answer 49

The most important atomic types include (1) xs:boolean, (2) xs:string, (3) xs:QName, (4) xs:date, (5) xs:time, (6) xs:datetime, (7) xs:float, (8) xs:double, (9) xs:decimal, (10) xs:integer.

Question 50

What are the numeric XQuery functions?

Answer 50

(1) ceiling, (2) floor, (3) round

Question 51

What are the string XQuery functions?

Answer 51

(1) concat, (2) contains, (3) substring, (4) string-length, (5) lower-case, and (6) upper-case.

Question 52

What are the bool XQuery functions?

Answer 52

(1) not, (2) true, and (3) false.

Question 53

What are the nodes XQuery functions?

Answer 53

(1) local-name, (2) namespace-uri

Question 54

What are the aggregate XQuery functions?

Answer 54

(1) count, (2) min, (3) max, (4) avg, (5) sum

Question 55

What are the data accessor XQuery functions?

Answer 55

(1) data and (2) string

Question 56

What are the SQL Server extension functions for XQuery?

Answer 56

(1) sql:column and (2) sql:variable

Question 57

What is an example of how you can use the aggregate XQuery functions?

Answer 57

SELECT @x.query('
for $i in //Customer
return 
< OrdersInfo >
   { $i/@companyname }
   < NumberOfOrders >
      { count($i/Order) }
   < /NumberOfOrders >
   < LastOrder >
      { max($i/Order/@orderid) }
   < /LastOrder >

Question 58

What are the parts of an XQuery path?

Answer 58

Every path consists of a sequence of steps listed from left to right. Steps are separated with slashes (/). A step may consist of 3 parts: (1) axis, (2) node test, (3) predicate. Here is the general form of a path: node-name/child::element-name[@attribute-name=value]

Question 59

What is an axis?

Answer 59

Specifies the direction of travel. There are 6 axes supported in SQL Server.

(1) child:: - Returns children of the current context node. This is the default axis and it can be omitted. Direction is down.
(2) descendant:: - Returns all descendents of the context node. Direction is down.
(3) self:: - Retrieves the context node. Direction is here.
(4) descendent-or-self:: (//) - Retrieves the context node and all its descendents. Direction is here and then down.
(5) attribute:: (@) - Retrieves the specified attribute of the context node. Direction is right.
(6) parent:: - Retrieves the parent of the context node. Direction is up.

Question 60

What is a node test?

Answer 60

Specifies criterion for selecting nodes. A node test generally follows the axis you specify. A node test can be as simple as a name test meaning you want nodes with that name. You can also use wildcards such as * or a node-kind test such as comment().

Question 61

What is a predicate?

Answer 61

Helps to further narrow down the search, e.g. a predicate such as [@attribute-name=value] selects only nodes with an attribute equal to a specific value.

Question 62

What does the * mean?

Answer 62

An asterisks () is a wildcard node test that means you want any principal node with any name. If you want all principal nodes in a namespace prefix, use prefix:. If you want all principal nodes named local-name regardless of namespace, you can use *:local-name.

Question 63

What is a principal node?

Answer 63

A principal node is the default node kind for an axis. e.g. the principal node is an attribute if the axis is attribute:: and it is an element for all other axes.

Question 64

What are the 4 node-kind test “methods”?

Answer 64

(1) comment() - selects comment nodes, (2) node() - true for any kind of node. greater than * which means any principal node, (3) processing-instruction - selects processing instruction nodes, (4) text() - selects text nodes or nodes without tags.

Question 65

What are numeric predicates?

Answer 65

Numeric predicates simply select nodes by position. You include them in brackets. e.g. /x/y[1] means the first y child element of each x element. You can also use parenthesis to apply a numeric predicate to the entire result of a path: (/x/y)[1] means the first element out of all nodes selected by x/y.

Question 66

What are boolean predicates?

Answer 66

Boolean predicates select all nodes for which the predicate evaluates to true. XQuery supports logical and/or operators. The operators work on both atomic values and sequences. For sequences, if one atomic value in a sequence leads to a true exit of the expression, the whole expression is evaluated to true, e.g. SELECT @x.query(‘(1,2,3) = (2,4)’); – true.

Question 67

What are value comparison operators and general comparison operators?

Answer 67

Value comparison operators do not work on sequences. They work on singletons. Trying to use them on a sequence will produce an error. They include: eq (=), ne (!=), lt (), ge (>=). General comparison operators work on sequences.

Question 68

Does XQuery support conditional if…then…else expressions?

Answer 68

Yes. But it is not used to change the program flow of the XQuery query. It is similar to the T-SQL CASE expression, e.g.

SELECT @x.query('
if (sql:variable("@v")="FirstName") then
 /Employee/FirstName
else
 /Employee/LastName
') AS FirstOrLastName;

Question 69

What are FLWOR expressions?

Answer 69

FLWOR is an acronym for for, let, where order by, and return. A FLWOR expression is actually a for each loop. You can use it to iterate through a sequence returned by an XPath expression.

Question 70

What should the name of the iterator variable in an FLWOR expression start with?

Answer 70

The name of the iterator variable must start with a dollar sign ($) in XQuery.

Question 71

What type of value does the order by clause need to return?

Answer 71

The expression passed to the order by clause must return a type compatible with the gt XQuery operator and it expects atomic values.

Question 72

What are braces { } used for in XQuery?

Answer 72

XQuery evaluates expressions in braces; without braces, everything would be treated as a string literal and returned as such.

Question 73

What are the parts of a FLWOR statement?

Answer 73

(1) For - Binds iterator variables to input sequences, (2) Let - Assigns a value to a variable for a specific iteration, (3) Where - Optional - Filters the iteration, (4) Order by - Controls the order in which the elements of the input sequence are processed, (5) Return - The return clause is evaluated once per iteration and it’s where you format the resulting XML of a query.

Question 74

What is an example of a FLOW expression?

Answer 74

SELECT @x.query('
for $i in CustomersOrders/Customer/Order
 let $j := $i/orderdate
 where $i/@orderid < 10900
 order by ($j)[1]
 return 

 {data($i/@orderid)}
 {$j}  ')

Question 75

What are sparse columns?

Answer 75

Sparse columns were introduced in SQL 2008. They are a solution for having attributes that are not applicable for all rows in a table. Sparse columns have optimized storage for NULLs. If you have to index them. you can efficiently use filtered indexes to index known values only.

Question 76

What is a column set?

Answer 76

A column set gives you access to all sparse columns at once through a column set. A column set is a representation of all the sparse columns that is even updatable.

Question 77

What are 5 notable methods for the XML data type?

Answer 77

(1) query() - querying. It returns an instance of an untyped XML value, (2) value() - retrieving atomic values, (3) exist() - checking existence, (4) modify() - modifying sections within the XML data as opposed to overwriting the whole thing, (5) nodes() - shredding xml data into multiple rows.

Question 78

What is the value() method for the XML data type?

Answer 78

The value method of the XML data type returns a scalar atomic value. It can be used anywhere scalar values are allowed. The value method accepts an XQuery expression as the first input parameter. The second parameter is the SQL Server data type returned. value must return a scalar value; therefore, you have to specify the position of the element in the sequence you’re browsing.

E.g. SELECT @x.value(‘(/CustomersOrders/Customer/companyname)[1]’, ‘VARCHAR(20)’)

Question 79

What is the exist() method for the XML data type?

Answer 79

You can use the exist method to test if a specific node exists in an XML instance. Typical usage of this clause is in the WHERE clause of T-SQL queries. The exist method returns a bit representing true or false. It will return 1 (true) if the XQuery expression in a query returns a non-empty result 0 (false) if the XQuery expression returns an empty result. Or NULL is the XML instance is NULL.

E.g. SELECT @x.exist(‘(/CustomersOrders/Customer/companyname)’)

Question 80

What is the modify() method for the XML data type?

Answer 80

You can use the modify method in a T-SQL UPDATE statement to change a small portion of XML data, e.g. a scalar value of some sub-element, instead of replacing the complete value. It’s a similar concept to the WRITE method available for VARCHAR(MAX) data types.

E.g.

SET @x.modify(‘replace value of /CustomersOrders[1]/Customer[1]/companyname[1]/text()[1] with “New Company Name”);

Question 81

What is the nodes method?

Answer 81

You can use the nodes method to shred an XML value into relational data. Its purpose is the same as OPENXML rowset function. However, the nodes method is faster than having to prepare the DOM (sp_xml_preparedocument), execute OPENXML, and then cleaning up (sp_xml_removedocument). The nodes method prepares the DOM internally. OPENXML approach could be faster if you prepared the DOM once and then shredded it multiple times in the same batch.

E.g.
SELECT T.c.value(‘./@orderid[1]’, ‘INT’) AS orderid
FROM @x.nodes(‘//Customer[@custid=1]/Order’) as T(c);

The nodes method has to be invoked for every row in the table. With the T-SQL APPLY operator, you can invoke a right table expression for every row of a left table expression.