XML Stream Query

In SQLServer2000, Microsoft released the Transact-SQL keywords FORXML and OPENXML. FORXML is an extension of the SELECT statement. It returns the XML stream query results shown in the following example. SELECTProductID, ProductNameFROMProductsProductFORXMLAUTO this query returns

In SQLServer2000, Microsoft released the Transact-SQL keywords FORXML and OPENXML. FORXML is an extension of the SELECT statement. It returns the XML stream query results shown in the following example. SELECT ProductID, ProductName FROM Products Product for xml auto this query returns

In SQL Server 2000, Microsoft released the Transact-SQL keywords FOR XML and OPENXML. For xml is an extension of the SELECT statement. It returns the XML streaming query results shown in the following example.
SELECT ProductID, ProductName
FROM Products Product
FOR XML AUTO
This query returns an XML snippet shown in the following example:


The OPENXML function is opposite to the for xml Condition Clause. It creates a rowset FOR the XML document, as shown in the following example:
DECLARE @ doc nvarchar (1000)
SET @ doc ='


'
DECLARE @ xmlDoc integer
EXEC sp_xml_preparedocument @ xmlDoc OUTPUT, @ doc
SELECT * FROM
OPENXML (@ xmlDoc, 'order/item', 1)
WITH
(OrderID integer '../@ OrderID ',
ProductID integer,
Quantity integer)
EXEC sp_xml_removedocument @ xmlDoc
Note the usage of using the sp_xml_preparedocument and sp_xml_removedocument stored procedures to create a memory display for the node tree of the XML document. The Transact-SQL code returns the following rowset.
OrderID ProductID Quantity
1011 1 2
1011 2 1

<无>

　　SELECT ProductID, ProductName 　　FROM Products Product 　　FOR XML AUTO

DECLARE @doc nvarchar(1000)SET @doc = '
     
      
     
  'DECLARE @xmlDoc integerEXEC sp_xml_preparedocument @xmlDoc OUTPUT, @docSELECT * FROMOPENXML (@xmlDoc, 'Order/Item', 1)WITH(OrderID integer '../@OrderID',ProductID integer,Quantity integer)EXEC sp_xml_removedocument @xmlDoc