SQL Server: XML Parser Optimization

DBA should create a stored procedure that accepts an xml input parameter and uses XML methods. The stored procedure would select the desired elements and attributes using the value() method in the SELECT list with the nodes() method specified in the FROM clause.
For example, for XML Parser Optimization DBA might use the following stored procedure to insert rows into the table:

CREATE PROCEDURE usp_InsertABLines (@xml xml)

AS
BEGIN
INSERT INTO ABDetails (InvID, Quantity, Cost, TaxFlag)
SELECT t.c.value('@InvID', 'int'), t.c.value('@Quantity', 'int'),
t.c.value('@Cost', 'money'), t.c.value('@Taxable', 'bit')
AS TaxFlag FROM @xml.nodes('/ABLines/ABLine')
AS t(c)
END

The nodes() method accepts an XQuery string and returns the specified nodes as a result set. In this scenario, the nodes() method returns a result set that contains one row for each purchase order line. Then, in the SELECT list, the table alias defined is used with the value() method to return the details for each line. 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 value using the XPath expression and returns it as the specified type. DBA could use the value() method to extract the desired data as a specified data type to be inserted into the ABDetails table. DBA should not use a SELECT statement that uses the query() XML method in the SELECT list. The query() method is used to query and retrieve XML elements and attributes from an XML instance as untyped XML. The method accepts a string XQuery expression that determines which elements and element attributes are extracted. In this scenario, DBA needed to return the data from the XML with specific data types, not in XML format.DBA should not create a user-defined function that accepts an xml input parameter and returns a table data type. DBA would still need to insert the value returned by the function into a table. DBA should not create a stored procedure that accepts an xml input parameter and uses OPENXML. Although DBA can use OPENXML to shred XML into relational data, OPENXML is more memory-intensive, and in this scenario, DBA wanted to minimize memory consumption. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. OPENXML can be used in place of a table or view in a SELECT statement. To use OPENXML, DBA first call the sp_xml_preparedocument system stored procedure to return a document handle. Then, DBA include a query that contains the OPENXML function in the FROM clause and include a WITH clause to identify the columns in the output. After the data has been extracted from the XML document, DBA call the sp_xml_removedocument stored procedure to remove the internal document handle and free resources.