SELECT
t.c.value('@ID[1]', 'int') AS
StationID, t.c.value('@Type[1]', '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 a single 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. DBA/Developer 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.DBA/Developer should not use the nodes() method. The
nodes() method accepts an XQuery string and returns all of the specified nodes
as a result set.
DBA/Developer
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.DBA/Developer 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.
No comments:
Post a Comment