Chapter 7 Querying and Managing XML Data Flashcards Preview

MSSQL Exam 70-461: Querying Microsoft SQL Server 2012 > Chapter 7 Querying and Managing XML Data > Flashcards

Flashcards in Chapter 7 Querying and Managing XML Data Deck (81)
Loading flashcards...
1
Q

What is XQuery?

A

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

2
Q

What are elements?

A

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

3
Q

What are tags?

A

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

4
Q

What is an XML document?

A

TODO

5
Q

What is a well-formed XML document?

A

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

6
Q

Are XML documents “ordered”?

A

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

7
Q

Is XML case-sensitive?

A

Yes. XML is case-sensitive unicode text.

8
Q

What is markup?

A

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

9
Q

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

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

What is XML CDATA?

A

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

11
Q

What is the prolog of an XML document?

A

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

12
Q

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

A

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

13
Q

What are attributes?

A

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

14
Q

What is an attribute centric presentation?

A

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

15
Q

What is an element centric presentation?

A

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

16
Q

Do element names need to be unique?

A

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

17
Q

What are namespaces?

A

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.

18
Q

What is a schema?

A

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.

19
Q

What is a typed XML document?

A

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

20
Q

How do you produce XML from relational data?

A

Use SELECT … FROM … FOR XML construct.

21
Q

What does the FOR XML RAW option do?

A

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.

22
Q

What does the FOR XML AUTO option do?

A

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.

23
Q

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

A

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

24
Q

What does the WITH NAMESPACES clause do?

A

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

25
Q

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

A

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.

26
Q

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

A

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.

27
Q

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

A

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

28
Q

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

A

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.

29
Q

What does the XMLSCHEMA option do?

A

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’);

30
Q

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

A

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’);

31
Q

What does the FOR XML EXPLICIT option do?

A

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.

32
Q

What does the FOR XML PATH option do?

A

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')
33
Q

What is an XPath expression?

A

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 (/).

34
Q

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

A

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.

35
Q

How do you convert XML data to relational data?

A

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.

36
Q

What is the OPENXML function?

A

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.

37
Q

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

A

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.

38
Q

What is an example of using all steps of OPENXML?

A

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;

39
Q

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

A

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.

40
Q

Is XQuery case-sensitive?

A

Yes. Like XML, XQuery is case-sensitive.

41
Q

What does XQuery return?

A

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.

42
Q

What is a QName?

A

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.

43
Q

What standard namespaces are defined in SQL Server?

A

(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.

44
Q

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

A

(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.

45
Q

What is the syntax for XQuery comments?

A

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.

46
Q

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

A

(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]
47
Q

What happens when you use the default element namespace?

A

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.

48
Q

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

A

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

49
Q

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

A

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.

50
Q

What are the numeric XQuery functions?

A

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

51
Q

What are the string XQuery functions?

A

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

52
Q

What are the bool XQuery functions?

A

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

53
Q

What are the nodes XQuery functions?

A

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

54
Q

What are the aggregate XQuery functions?

A

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

55
Q

What are the data accessor XQuery functions?

A

(1) data and (2) string

56
Q

What are the SQL Server extension functions for XQuery?

A

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

57
Q

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

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

What are the parts of an XQuery path?

A

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]

59
Q

What is an axis?

A

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.

60
Q

What is a node test?

A

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().

61
Q

What is a predicate?

A

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.

62
Q

What does the * mean?

A

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.

63
Q

What is a principal node?

A

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.

64
Q

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

A

(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.

65
Q

What are numeric predicates?

A

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.

66
Q

What are boolean predicates?

A

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.

67
Q

What are value comparison operators and general comparison operators?

A

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.

68
Q

Does XQuery support conditional if…then…else expressions?

A

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;
69
Q

What are FLWOR expressions?

A

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.

70
Q

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

A

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

71
Q

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

A

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

72
Q

What are braces { } used for in XQuery?

A

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

73
Q

What are the parts of a FLWOR statement?

A

(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.

74
Q

What is an example of a FLOW expression?

A
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}  ')
75
Q

What are sparse columns?

A

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.

76
Q

What is a column set?

A

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.

77
Q

What are 5 notable methods for the XML data type?

A

(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.

78
Q

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

A

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)’)

79
Q

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

A

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)’)

80
Q

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

A

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”);

81
Q

What is the nodes method?

A

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.