Exam Prep Questions

You are working with a table structure that contains a column that has the xml data type. You need to alter the contents of the column. What method of the xml data type do you use?

• A. query()

• B. value()

• C. exist()

• D. modify()

• E. nodes()

You are producing a report that will combine the contents from xml data type and SQL data type columns . Which method of the xml data type do you use?

• A. query()

• B. value()

• C. exist()

• D. modify()

• E. nodes()

You are trying to connect to an endpoint to perform an ad hoc SQL query. You are able to connect, but when you try a query, it fails to execute. Your endpoint creation script looks as follows :

CREATE ENDPOINT sql_endpoint
STATE = STARTED
AS HTTP(PATH = '/sql',
   AUTHENTICATION = (INTEGRATED),
   PORTS = ( CLEAR ),
   SITE = 'SERVER')
FOR SOAP ( WEBMETHOD 'GetSqlInfo'
           (name='master.dbo.xp_msver',
            SCHEMA=STANDARD ),
            WEBMETHOD 'DayAsNumber'
            (name='master.sys.fn_MSdayasnumber'),
   WSDL = DEFAULT,
   SCHEMA = STANDARD,
   DATABASE = 'master',
   NAMESPACE = 'http://tempUri.org/')

You need to store a large amount of data in an xml data type column of a database. The column must be indexed. You want to establish the fewest possible indexes and still provide the best performance for queries that utilize a lot of XQuery path operations. How would you set up the indexing? (Select two answers.)

• A. Create a standard clustered index on the primary key field.

• B. Create a primary XML index on the xml data type column.

• C. Create a secondary XML index configured for properties.

• D. Create a secondary XML index configure for paths.

• E. Create a secondary XML index configured for values.

You are executing a query that needs to provide the results, with no attributes at all. The results should contain the namespace for the data. Which option would you use?

• A. FOR XML

• B. FOR XML RAW

• C. FOR XML RAW, ELEMENTS

• D. FOR XML RAW, ELEMENTS XSINIL

• E. FOR XML AUTO

D. You use the modify() method in the SET clause of an UPDATE statement to alter the content of an XML document. query() is used to return untyped XML from a positioned query. value() returns the SQL data from within the defined XML. exist() determines whether XML exists to match up with the positioned query provided. nodes() is used as the source for a query that returns multiple pieces of data for reporting purposes.

B. value() returns the SQL data from within the defined XML. This would allow you to combine it with other SQL data. You use the modify() method within the SET clause of an UPDATE statement to alter the content of an XML document. query() is used to return untyped XML from a positioned query. exist() determines whether XML exists to match up with the positioned query provided. nodes() is used as the source for a query that returns multiple pieces of data for reporting purposes.

D. The default setting for SQL batches that allow for ad hoc queries is disabled. In the preceding endpoint definition, no setting is supplied, and therefore the ad hoc query capability is turned off.

B, D. To create a secondary XML index, you must first have a primary XML index created. The best secondary index in this scenario is for paths. An index configured for values would perform well in queries that do not provide path information. Queries based on multiple XML values would benefit from a properties index.

D. The ELEMENTS keyword provides all the output in element format, with no attributes. XSINIL adds the namespace prefix to the resulting XML.