You manage a database on an instance of SQL Server 2008 for a large training company.
You have the following Transact-SQL:
DECLARE @xmldoc xml
SET @xmldoc=’
<EventData>
<Event ID="C-012" EventDesc="Using Microsoft Word 2007" EventLoc="Southeast Campus">
<Coordinator>Beth Jackson</Coordinator>
<AvailSeats>35</AvailSeats>
<Enrolled>10</Enrolled>
</Event>
<Event ID="C-057" EventDesc="SQL Server 2008 Basics" EventLoc="Main Campus"><Coordinator>Ross Moore</
Coordinator><AvailSeats>30</AvailSeats><Enrolled>15</Enrolled></Event><Event ID="M-023" EventDesc="Time Management 101"
EventLoc="Adair Center"><Coordinator>Helen Lewis</Coordinator><AvailSeats>45</AvailSeats><Enrolled>30</Enrolled></Event>
</EventData>’
You want to extract data from the XML document and insert it into a new table named EventList. The new table should contain a row for each event that includes the following columns:
EventID - The ID attribute value for the event
Description - The EventDesc attribute value for the event
Location - The EventLoc attribute value for the event
Which Transact-SQL statement should you use?
A. SELECT x.Event.value(‘@ID[1]’, ‘varchar(10)’) AS EventID,x.Event.value(‘@EventDesc[1]’, ‘varchar(35)’) AS
Description,x.Event.value(‘@EventLoc[1]’, ‘varchar(25)’) AS Location
INTO EventList
FROM @xmldoc.nodes(‘//EventData/Event’) AS x(Event);
B. SELECT
@xmldoc.query(‘/EventData/Event[@ID]’) AS EventID,@xmldoc.query(‘/EventData/Event[@EventDesc]’) AS
Description,@xmldoc.query(‘/EventData/Event[@EventLoc]’) AS Location
INTO EventList
FROM @xmldoc.nodes(‘//EventData/Event’) AS x(Event);
C. SELECT
@xmldoc.query(‘/EventData/Event/@ID’) AS EventID,@xmldoc.query(‘/EventData/Event/@EventDesc’) AS
Description,@xmldoc.query(‘/EventData/Event/@EventLoc’) AS Location
INTO EventList
FROM @xmldoc.nodes(‘//EventData/Event’) AS x(Event);
D. SELECT
@xmldoc.value(‘/EventData/Event/@ID’, ‘varchar(10)’) AS EventID,@xmldoc.value(‘/EventData/Event/@EventDesc’, ‘varchar (35)’) AS Description,@xmldoc.value(‘/EventData/Event/@EventLoc’, ‘varchar(25)’) AS Location
INTO EventList
FROM @xmldoc.nodes(‘//EventData/Event’) AS x(Event);
Correct Answer: A
Explanation/Reference:
In this scenario, the query uses the value() XML method to extract values of specific elements and attributes. The value() method accepts two arguments. The first argument is a string XQuery expression, and the second argument is a string containing a SQL Server data type. The value() method extracts a single value using the XPath expression and returns it as the specified type. In this scenario, the statement uses the value() method to return an EventID column containing a varchar(10) value extracted from the ID attribute in the <Event> element of the XML. The statement also uses the value() method to return a Description column containing a varchar(35) value extracted from the EventDesc attribute in the <Event> element of the XML. The [1] at the end of each string expression indicates that the information is extracted from a single element. The value() method must return a single value. However, in this scenario, the XML contains multiple events. Therefore, the correct query includes the nodes() XML method in the FROM clause of the query. The nodes() method accepts an XQuery string and returns all of the specified nodes as a result set. In this scenario, the nodes() method returns a result set that contains one row for each event. Each row contains the corresponding XML for the event. Then, in the SELECT list, the table alias defined is used with the value() method to return the details for each specific event. You could also use the nodes() method to separate XML. In this scenario, the statement would display results similar to the following:
You should not use the following query:
SELECT @xmldoc.value(‘/EventData/Event/@ID’, ‘varchar(10)’) AS EventID,@xmldoc.value(‘/EventData/Event/@EventDesc’, ‘varchar(35)’) AS Description,@xmldoc.value(‘/EventData/Event/@EventLoc’, ‘varchar(25)’) AS LocationINTO EventList;
The value() method must return a single value. This query omitted the [1] at the end of each expression that indicates a single value is returned. If you specify an expression that returns multiple values, or an expression that cannot be successfully converted to the specified SQL Server data type, an error occurs. This query will return the following error:
Msg 2389, Level 16, State 1, Line 25XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’
You should not use the following query:
SELECT @xmldoc.query(‘/EventData/Event[@ID]’) AS EventID,@xmldoc.query(‘/EventData/Event[@EventDesc]’) AS
Description,@xmldoc.query(‘/EventData/Event[@EventLoc]’) AS LocationINTO EventList;
This query uses the query() method. The query() method is used to query and retrieve XML elements and attributes from an XML instance. The method accepts a string XQuery expression that determines which elements and element attributes are extracted. This query will execute successfully, but will not produce the desired results. This query will insert one row into the EventList table that contains three columns, EventID, Description, and Location. However, each column will contain the following XML:
<Event ID="C-012" EventDesc="Using Microsoft Word 2007" EventLoc="Southeast Campus"><Coordinator>Beth Jackson</Coordinator><AvailSeats>35</AvailSeats><Enrolled>10</Enrolled></Event><Event ID="C-057" EventDesc="SQL Server 2008
Basics" EventLoc="Main Campus"><Coordinator>Ross Moore</Coordinator><AvailSeats>30</AvailSeats><Enrolled>15</Enrolled></Event><Event ID="M-023" EventDesc="Time Management 101" EventLoc="Adair Center"><Coordinator>Helen Lewis</Coordinator><AvailSeats>45</AvailSeats><Enrolled>30</Enrolled></Event>
You should not use the following query:
SELECT @xmldoc.query(‘/EventData/Event/@ID’) AS EventID,@xmldoc.query(‘/EventData/Event/@EventDesc’) AS
Description,@xmldoc.query(‘/EventData/Event/@EventLoc’) AS LocationINTO EventList;
This statement generates the following syntax error because the XQuery expression is not valid:
Msg 2396, Level 16, State 1, Line 25XQuery [query()]: Attribute may not appear outside of an element
