XML

Question 1

What are the four modes of XML

Answer 1

• Auto
• Raw - Attribute Centric
• Path
• Explicit - Deprecated

Question 2

what is the difference between XML Fragments and an XML Document?

Answer 2

An XML Document has a single root node.

XML Fragments have no root node.

Question 3

What is well formed XML

Answer 3

This means every begin tag has a corresponding end tag and they are nested properly.

<product></product>

<product></product>

<product></product>

Question 4

What is XSD?

Answer 4

XML Schema Description.

One of the most widely used forms for storing meta data descriptiions in xml.

XSD Documents are XML documents that describe the meta data of other XML documents.

XSD predefines element names, data types, number of occurences of an element, the contraints, and more.

A predefined XML document is referred to as a ‘typed’ XML document.

Question 5

Describe attribute centric xml

Answer 5

One row per row of data.

Attributes have their own name and their values are enclosed in quotes.

Question 6

Describe Element centric xml

Answer 6

One tag per element

Question 7

Is XML case sensitive?

Answer 7

Yes.

it is case sensitive unicode text

Question 8

List the special value characters.

Answer 8

Character = Replacement Text

& (ampersand) = &

” (quotation mark) = "

< (less than) = <

> (greater than) = >

’ (apostrophe) = '

Question 9

Recite the XML CDATA

Answer 9

…}}>

Replace the … with any text that is not a string literal.

Question 10

Do elements need to be uniquely named?

Answer 10

No.

To seperate similarly named elements use namespaces.

Question 11

Recite FOR XML RAW

Answer 11

SELECT

FROM dbo.Product

FOR XML RAW; –> Attribute Centric

SELECT

FROM dbo.Product

WHERE

ORDER BY

FOR XML RAW (‘ProductID’), ELEMENTS, ROOT(‘Product’);

Changes:

1. The row element name to ‘ProductID’,
2. The FOR XML RAW default from Attribute Centric into Element Centric
3. Adds a root node and namespace (‘Product’)

Question 12

Recite FOR XML AUTO

Answer 12

WITH NAMESPACES(‘ER70761-CustomersOrders’ AS co)

SELECT

[co:Customer].custid AS [co:custid],

[co:Customer].companyname AS [co:companyname],

[co:Order].orderid AS [co:orderid],

[co:Order].orderdate AS [co:orderdate]

FROM Sales.Customers AS [co:Customer]

JOIN Sales.Orders AS [co:Order]

ON [co:Customer].custid = [co:Order].custid

WHERE…

ORDER BY

[co:Customer].custid AS [co:custid],

[co:Order].orderid AS [co:orderid]

FOR XML AUTO, ELEMENTS, ROOT(‘CustomerOrders’);

The table and column aliases are used to create the element names. The element names are prefixed with the namespace name.

The results:

<customerorders></customerorders>

<customer></customer>

<custid>1</custid>

<companyname>Customer NRZBB</companyname>

<order></order>

<orderid>10692</orderid>

<orderdate>2018-10-23</orderdate>

<custid>2</custid>

<companyname>Customer NRZCC</companyname>

<order></order>

<orderid>10693</orderid>

<orderdate>2018-10-23</orderdate>

The Order By is important because it formats the returned XML; without it the order is not predictable.

Also the SELECT statement order is important as it is used to determine the order of nesting elements. The column order need to follow a one-to-many relationship. A customer can have many orders, therefore custid preceeds the orderid column.

Question 13

What is converting XML into a table called?

What is used to convert XML into a table?

Answer 13

XML is converted into a relational table is called ‘shredding’ XML.

To shred XML use the OPENXML rowset function.

Question 14

When shredding XML, what is the function used and what is process?

Answer 14

The function OpenXML is used to create a rowset over an in-memory XML document using the Document Object Model (DOM) presentation.

To process the DOM

1. Declare an integer for the DOM handle
2. Declare a NVarchar for the XML document and set it to the XML (copy…paste)
3. Execute the sys.sp_xml_preparedocument @DocHandle OUTPUT, @XMLDocument;
4. SELECT *

FROM OPENXML

(

@DocHandle,

‘/CustomerOrder/Customer’,

(1,2,8, or 11) )

• 1 = Attribute Centric
• 2 = Element Centric
• 8 is combined with either 1 or 2 to create

WITH

(

custid INT,

companyname NVarchar(40)

);

1. Remove the DOM by…

EXECUTE sys.sp_xml_removedocument @DocHandle;

Question 15

What is XQUERY?

Answer 15

XQUERY is standard language for browsing XML instances and returning XML output.

It is richer than XPATH (older).

XQUERY can loop over nodes, shape the returned XML instance, and more.

Question 16

What is XQUERY’s FLWOR

Answer 16

FLWOR is an acronym: FOR, LET, WHERE, ORDER BY, and RETURN. This allows for iterating thru sequences returned by an XPATH expression.

• FOR: Binds iterator variables to input sequences. Input sequences are either sequences of nodes or sequences of atomic values. Atomic value sequences are created using literals or functions.
• LET: (Optional) Assigns a value to a variable for a specific iteration. The expression can return a sequence of nodes or atomic values.
• WHERE: (Optional) Filters the iteration
• ORDER BY: Controls the order in which the elements of the input sequence are processed.
• RETURN: Evaluated once per iteration. The results are returned to the client in iteration order. This is a formatting of the resulting XML.

Question 17

Recite XQUERY Expression

Answer 17

DECLARE @X AS XML = N’

<customerorders></customerorders>

<customer></customer>

Question 18

What is the XML Data Type?

Answer 18

The XML Data Type is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.

CONTENT
Restricts the xml instance to be a well-formed XML fragment. The XML data can contain multiple zero or more elements at the top level. Text nodes are also allowed at the top level.

This is the default behavior.

DOCUMENT
Restricts the xml instance to be a well-formed XML document. The XML data must have one and only one root element. Text nodes are not allowed at the top level.

xml_schema_collection
Is the name of an XML schema collection. To create a typed xml column or variable, you can optionally specify the XML schema collection name.

Remarks

The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.

DECLARE @DemographicData XML (Person.IndividualSurveySchemaCollection);

SET @DemographicData

=

(

SELECT TOP 1 Demographics

FROM Person.Person

);

SELECT @DemographicData;

GO

Question 19

What is the xml_schema_namespace

Answer 19

The xml_schema_namespace reconstructs all the schemas or a specific schema in the specified XML schema collection. This function returns an xml data type instance.

xml_schema_namespace( Relational_schema , XML_schema_collection_name , [Namespace] )

Remarks

When you import XML schema components in the database by using CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION, aspects of the schema used for validation are preserved. Therefore, the reconstructed schema may not be lexically the same as the original schema document. Specifically, comments, white spaces, and annotations are lost; and implicit type information is made explicit.

Example,

<element></element>

becomes

<element></element>

.

Also, namespace prefixes are not preserved.

If you specify a namespace parameter, the resulting schema document will contain definitions for all schema components in that namespace, even if they were added in different schema documents or DDL steps, or both.

You cannot use this function to construct XML schema documents from the sys.sys XML schema collection.

SELECT xml_schema_namespace

(

N’production’,

N’ProductDescriptionSchemaCollection’

);

Question 20

What is an XML Data Type Method?

What are the XML Data Type Methods?

Answer 20

xml data type methods are used to query an XML instance stored in a variable or column of xml type.

1. QUERY()
2. VALUE()
3. EXIST()
4. MODIFY()
5. NODES()

Question 21

What is the QUERY() method and how is it used?

Answer 21

Specifies an XQuery against an instance of the xml data type. The result is of xml type. The method returns an instance of untyped XML.

Syntax: query (‘XQuery’)

‘XQuery’
Is a string , an XQuery expression, that queries for XML nodes such as elements, attributes, in an XML instance.

Examples

Using the query() method against an xml type variable

The following example declares a variable @myDoc of xml type and assigns an XML instance to it. The query()method is then used to specify an XQuery against the document.

The query retrieves the <features> child element of the <productdescription> element:</productdescription></features>

DECLARE @myDoc xml

set @myDoc =

‘<root> <productdescription> <features> <warranty>1 year parts and labor</warranty> <maintenance>3 year parts and labor extended maintenance is available</maintenance> </features> </productdescription> </root>’

SELECT @myDoc.query(‘/Root/ProductDescription/Features’)

This is the result:

<features></features>

<warranty>1 year parts and labor</warranty>

<maintenance>3 year parts and labor extended maintenance is available</maintenance>

Question 22

What is the VALUE() method?

Answer 22

The VALUE() Method performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.

You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

Syntax: value (XQuery, SQLType)

‘XQuery’ is the XQuery expression, a string literal, that retrieves data inside the XML instance. The XQuery must return at most one value. Otherwise, an error is returned.

‘SQLType’ is the preferred SQL type, a string literal, to be returned. The return type of this method matches the SQLType parameter. SQLType cannot be an xml data type, a common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type. SQLType can be an SQL, user-defined data type.

The value() method uses the Transact-SQL CONVERT operator implicitly and tries to convert the result of the XQuery expression, the serialized string representation, from XSD type to the corresponding SQL type specified by Transact-SQL conversion.

Note

For performance reasons, instead of using the value() method in a predicate to compare with a relational value, use exist() with sql:column().

Examples

A. Using the value() method against an xml type variable

In the following example, an XML instance is stored in a variable of xml type. The value() method retrieves the ProductID attribute value from the XML. The value is then assigned to an int variable.

DECLARE @myDoc xml

DECLARE @ProdID int

SET @myDoc =

‘<root> <productdescription> <features> <warranty>1 year parts and labor</warranty> <maintenance>3 year parts and labor extended maintenance is available</maintenance> </features> </productdescription> </root>’ SET @ProdID = @myDoc.value(‘(/Root/ProductDescription/@ProductID)[1]’,

‘int’ )

SELECT @ProdID

Value 1 is returned as a result.

Although there is only one ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional [1] is specified at the end of the path expression.