You are a database developer for at large mail processing facility. You have a Prod database that contains tables used by many in-house applications, as well as third-party applications.
You have an XML document that contains the following:
<Root>
<Station ID="12" Type="Collation">
<PieceCount>5512</PieceCount>
<CustomerID>30</CustomerID>
</Station>
<Station ID="25" Type="Postal Sort">
<PieceCount>2503</PieceCount>
<CustomerID>30</CustomerID>
</Station>
<Station ID="14" Type="Folder">
<PieceCount>10548</PieceCount>
<CustomerID>65</CustomerID>
</Station>
<Station ID="12" Type="Insertion">
<PieceCount>27690</PieceCount>
<CustomerID>49</CustomerID>
</Station>
</Root>
You are creating a query to retrieve data from the XML and insert it into a temporary table.
Which XML method should you use in the SELECT list of your query?
A. query()
B. value()
C. nodes()
D. modify()
Correct Answer: B
Explanation/Reference:
You should use the value() method in the SELECT list of your query. 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. For example, in this scenario, you could load the XML into an xml variable named @x and use the following statement to insert rows into a temporary table:
SELECT
t.c.value(‘@ID’, ‘int’) AS StationID,
t.c.value(‘@Type’, ‘varchar(35)’) AS ProcessType,
t.c.value(‘PieceCount[1]’, ‘int’) AS NumPieces
INTO #PieceCounts
FROM @x.nodes(‘//Root/Station’) AS t(c);
With this statement, the value() method extracts values of the ID and Type attributes of the <Station> element and the value of the <PieceCount> element. Each element or attribute specified is returned with the specified data type, and the INTO clause inserts the result into a temporary table named #PieceCounts. The [1] at the end of each string expression indicates that the information is extracted from the first element. The value() method must return a single value. However, in this scenario, the XML contains multiple <Station> elements. Therefore, the nodes() method must be used in the FROM clause. With the given statement, the following data would be inserted into the #PieceCounts table.
You should not use the query() method. The query() method is used to query and retrieve XML elements and attributes from an XML instance as untyped XML, not a relational result set. The method accepts a string XQuery expression that determines which elements and element attributes are extracted.
You should not use the nodes() method. The nodes() method accepts an XQuery string and returns all of the specified nodes as a result set. You can use the nodes() method in the FROM clause of a query to process multiple XML nodes using the value() method in the SELECT list.
You should not use the modify() method. The modify() method is used to change the value of an xml type variable or column. The modify() method can only be invoked using the SET clause of an UPDATE statement. The method accepts a string argument containing an XML Data Manipulation Language (DML) statement, such as replace value of, insert, or delete. The statement is then used to update the corresponding element or attribute in the XML.
