nerdexam
Microsoft

70-467 · Question #135

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schemal.Table1. You have a stored procedure named Schemal.Proc1 that accepts an XML parameter named Param1…

The correct answer is A. Define an XML column in Table1 by using an XML schema collection. B. Create an XML schema collection in the database from the text file. D. use the modify method to insert the XML schema into each row of the XML column in Table1. Storing validated XML in SQL Server requires creating an XML schema collection from the XSD, binding it to the table column, and using the XML modify() method to insert data - the variable declaration syntax described in C is invalid.

Role of Transact-SQL and the Database Engine in Enterprise IT

Question

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schemal.Table1. You have a stored procedure named Schemal.Proc1 that accepts an XML parameter named Param1. You need to store validated XML data in Schemal.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Options

  • ADefine an XML column in Table1 by using an XML schema collection.
  • BCreate an XML schema collection in the database from the text file.
  • CDeclare Param1 var1 as type XML and associate the variable to the XML schema collection.
  • Duse the modify method to insert the XML schema into each row of the XML column in Table1.

How the community answered

(20 responses)
  • A
    80% (16)
  • C
    20% (4)

Why each option

Storing validated XML in SQL Server requires creating an XML schema collection from the XSD, binding it to the table column, and using the XML modify() method to insert data - the variable declaration syntax described in C is invalid.

ADefine an XML column in Table1 by using an XML schema collection.Correct

Defining the XML column in Table1 with an associated XML schema collection (declared as XML(SchemaCollectionName)) enforces type validation on every value stored in that column, rejecting any XML that does not conform to the registered schema.

BCreate an XML schema collection in the database from the text file.Correct

Creating the XML schema collection in the database from the XSD text file using CREATE XML SCHEMA COLLECTION is the prerequisite step that makes the schema available to reference in column and parameter definitions.

CDeclare Param1 var1 as type XML and associate the variable to the XML schema collection.

The syntax 'Param1 var1 as type XML' is not valid Transact-SQL; to associate a stored procedure XML parameter with a schema collection, the parameter must be declared directly as XML(SchemaCollectionName) in the procedure signature, not via a separate local variable with an extra identifier.

Duse the modify method to insert the XML schema into each row of the XML column in Table1.Correct

The modify() method of the XML data type is the correct mechanism for performing DML operations - such as inserting nodes - on existing XML column values using XQuery expressions within a typed XML column.

Concept tested: XML schema collection for typed XML column validation in SQL Server

Source: https://learn.microsoft.com/en-us/sql/relational-databases/xml/xml-schema-collections-sql-server

Topics

#XML schema collection#XSD#typed XML#XML validation

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice