70-461 Sql Server

Front 1

How COALESCE function is similar to CASE expression

Back 1

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax is:

COALESCE ("expression 1", "expressions 2", ...)

It is the same as the following CASE statement:

SELECT CASE ("column_name")

WHEN "expression 1 is not NULL" THEN "expression 1"

WHEN "expression 2 is not NULL" THEN "expression 2"

... [ELSE "NULL"] END

FROM "table_name"; -

Front 2

How do you convert table data into XML nodes?

Back 2

-- create XML structure using FOR XML AUTO

SELECT BusinessEntityID, PersonType, Title, FirstName, MiddleName, LastName, Suffix

FROM Person.Person

WHERE BusinessEntityID = 10001

FOR XML AUTO

GO

RESULT EXAMPLE???

Front 3

What is the use of ELEMENTS argument?

Back 3

The ELEMENTS argument causes every value to be created as a node element.

Front 4

What does PATH argument do?

Back 4

Allows to modify the root node name.

Following query returns the root node name as 'Person' :

SELECT BusinessEntityID,

PersonType,

Title,

FirstName,

MiddleName,

LastName,

Suffix

FROM Person.Person

WHERE BusinessEntityID = 10001

FOR XML PATH('Person')

GO

Front 5

How do you make a value as node attribute?

Back 5

We can create node attribute values by simply designating column aliases that use the ‘@’ symbol.

The following query results in a combination of node attributes and elements

-- designate node attribute

SELECT BusinessEntityID AS '@ID', PersonType, Title, FirstName, MiddleName, LastName, Suffix

FROM Person.Person

WHERE BusinessEntityID = 10001

FOR XML PATH('Person')

GO

Front 6

If a table contains an XML field as XML data type. how that field is represented in the returned data?

Back 6

The XML field is added as a nested node element. the XML namespace data is included in the nested node.

Front 7

Creating XML vs Shredding XML

Back 7

Shredding XMLmeans to strip the actual data away from the markup tags, and organize it into a relational format. For example, shredding is what happens when an XML document is imported into a table, when each node value is mapped to a specific field in the table.

Creating XML to organize into XML format from relational format.

Front 8

OPENXML() or XQuery Methods

Back 8

OPENXML() or XQuery Methods are used to shred XML. OPENXML() might be more faster for larger data operations, but they are more complex to use and more memory intensive. OPENXML() cannot take advantage of XML indexes

Front 9

WITH XMLNAMESPACES clause?

Back 9

a WITH XMLNAMESPACES clause is used to declare the XML namespace only once for the entire code block:

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)

SELECT FirstName, MiddleName, LastName, Demographics.value('(/ns:IndividualSurvey/ns:Occupation)[1]','varchar(50)') AS Occupation, Demographics.value('(/ns:IndividualSurvey/ns:Education)[1]','varchar(50)') AS Education, Demographics.value('(/ns:IndividualSurvey/ns:HomeOwnerFlag)[1]','bit') AS HomeOwnerFlag, Demographics.value('(/ns:IndividualSurvey/ns:NumberCarsOwned)[1]','int') AS NumberCarsOwned

FROM Person.Person

WHERE BusinessEntityID = 15291

GO

Example without WITH XMLNAMESPACES clause?

Front 10

nodes() vs value() performance

Back 10

The XQuery value() method is an easy way to extract values from XML data while preserving the data types.

he XQuery nodes() method is another option that allows us to specify a particular node set in which to look for the desired child nodes.

Nodes() can be used in more complex queries.

Comparision EXAMPLE???

Front 11

XQuery query('/')

Back 11

starting at the root of each XML instance .

-- combine store survey XML data

SELECT Name, BusinessEntityID AS ID, Demographics.query('/')

FROM Sales.Store AS Store

WHERE SalesPersonID = 282

FOR XML AUTO

GO

Front 12

ROOT argument vs PATH

Back 12

Both have same uses except ROOT can be used with the AUTO mode whereas PATH is just an alternative of the AUTO mode.

Front 13

RAW vs AUTO vs PATH vs EXPLICIT modes

Back 13

These are the four modes used with the FOR XML clause.

The RAW mode generates a single XML element for each row in the result set returned by the query:

AUTO mode returns query results as nested XML elements. This does not provide much control over the shape of the XML generated from a query result. The AUTO mode queries are useful if you want to generate simple hierarchies.

the order in which column names are specified in the SELECT clause is significant. The first, leftmost table that is identified forms the top element in the resulting XML document. The second leftmost table, identified by columns in the SELECT statement, forms a subelement within the top element, and so on.

SELECT Employee.EmployeeID, ContactInfo.FirstName, ContactInfo.MiddleName, ContactInfo.LastNameFROM HumanResources.Employee AS Employee INNER JOIN Person.Contact AS ContactInfo ON ContactInfo.ContactID = Employee.ContactIDWHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees');

RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. EXPLICIT mode queries can be cumbersome using PATH mode is a better alternative.

PATH EXAMPLE??

Front 14

When to use XML data type vs large object storage-VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX)

Back 14

if you simply store your XML documents in the database and retrieve and update those documents as a whole-that is, if you never need to query or modify the individual XML components-you should consider using one of the large object data types. The same goes for XML files that you want to preserve in their original form, such as legal documents. If you need to retain an exact textual copy, use large object storage.

But the rest of the time, you should consider the XML data type.

Front 15

What are the limitations of using XML data type?

Back 15

1. XML data type instances cannot exceed 2 GB.

2. It does not support casting or converting to either text or ntext.

3. It can't be compared or sorted, hence can't be used with the GROUP BY clause.

4. It cannot be used as a key column in an index.

Front 16

Untyped XML objects vs Typed XML objects

Back 16

Typed XML type have XML schemas to validate the Data where as Untyped XML type does not have schema.

untyped XML example:

CREATE TABLE dbo.Resumes( CandidateID INT IDENTITY PRIMARY KEY, CandidateResume XML ;

typed XML example:

CREATE TABLE dbo.Resumes( CandidateID INT IDENTITY PRIMARY KEY, CandidateResume XML (HumanResources.HRResumeSchemaCollection));

The typed XML is like a contract: I promise, that you'll find XML-data of a given structure.This is the base for XML indices, query optimizations and other fancy things.The more SQL Server knows in advance, the better it will create its execution plans.

Front 17

FOR XML vs OPENXML()

Back 17

FOR XML is used for creating XML. Open XML used for shredding XML.