XML - Pro SQL Server Internals - page 225

OPENXML
OPENXML is another way of dealing with XML data in SQL Server. It utilizes the MSXML parser ( Msxmlsql.dll ), and
it keeps documents in memory cache, which can utilize up to one-eighth of SQL Server memory.
All XML documents needs to be parsed individually using the sp_xml_preparedocument stored procedure.
As a result, you cannot use OPENXML to process XML data from multiple table rows. For single XML documents,
OPENXML outperforms XQuery, although OPENXML's memory usage pattern makes it a dangerous choice. You can
lose a large amount of SQL Server memory if your code does not remove documents from cache by using the
sp_xml_removedocument stored procedure. I suggest avoiding OPENXML unless the performance of XQuery is
insufficient for the task to be performed. For more information about OPENXML, read this article:
http://msdn.microsoft.com/en-us/library/ms186918.aspx .
FOR XML
You can retrieve the results of the SELECT query in XML format by using the FOR XML clause. There are four modes that
control the shape of the generated XML: RAW , AUTO , EXPLICIT , and PATH . I recommend that you use the PATH mode
when you need to generate XML for a complex shape. The code shown in Listing 11-19 demonstrates using FOR XML
PATH to accomplish this.
Listing 11-19. Using the FOR XML PATH
declare
@Orders table
(
OrderId int not null primary key,
CustomerId int not null,
OrderNum varchar(32) not null,
OrderDate date not null
)
declare
@OrderLineItems table
(
OrderId int not null,
ArticleId int not null,
Quantity int not null,
Price float not null,
primary key(OrderId, ArticleId)
)
insert into @Orders(OrderId, CustomerId, OrderNum, OrderDate)
values
(42,123,'10025','2013-07-15T10:05:20'),
(54,25,'10032','2013-07-15T11:21:00')
insert into @OrderLineItems(OrderId, ArticleId, Quantity, Price)
values
(42,250,3,9.99), (42,404,1,19.99),
(54,15,1,14.99), (54,121,2,6.99)