Using XML to pass and return values to procs

DECLARE @rslt NVARCHAR(MAX)
SELECT @rslt = CONVERT(NVARCHAR(MAX), x.rslt )
FROM
(
SELECT
'A' 'status',
MAX(MSG.id) 'msg/id',
MAX(MSG.TEXT) 'msg/text',
(SELECT
entity_ver_id 'entity/@id',
ENTITY_CAT_ID 'entity/cat',
client_id 'entity/client_id',
ver_name 'entity/entity_name',
ver_desc 'entity/entity_desc',
create_date 'entity/entity_date',
created_by_user_pin 'entity/entity_creator',
entity_ver_id 'entity/entity_ver',
entity_amt 'entity/entity_amt',
(SELECT
req_id 'request/@id',
req_date 'request/req_date',
amt 'request/req_amt',
priority_code 'request/req_priority',
Entr_by_pin 'request/entr_by_id',
entr_by_name 'request/entr_by_name',
Req_by_pin 'request/requestor_id',
requestor_name 'request/requestor_name',
[status] 'request/req_current_status',
status_date 'request/req_current_status_date',
req_cmnt 'request/req_cmnt',
(SELECT
resp_id 'response/@id',
resp_date 'response/resp_date',
Resp_user_pin 'response/resp_user_id',
respondent_name 'response/resp_name',
resp_type 'response/resp_type',
final_approver 'response/final_approver',
resp_status 'response/resp_status',
(
SELECT
CASE WHEN RESP_USER_PIN IS NULL or COALESCE(resp_delegator_user_pin,RESP_USER_PIN) = RESP_USER_PIN THEN
'N'
else
'Y'
end 'delegated_ind',
CASE WHEN RESP_USER_PIN IS NULL or COALESCE(resp_delegator_user_pin,RESP_USER_PIN) = RESP_USER_PIN THEN
CAST(NULL AS VARCHAR(30))
ELSE
resp_delegator_user_pin
end 'delegator_pin',
CASE WHEN RESP_USER_PIN IS NULL or COALESCE(resp_delegator_user_pin,RESP_USER_PIN) = RESP_USER_PIN THEN
CAST(NULL AS VARCHAR(30))
ELSE
Delegator_name
end 'delegator_name'
FROM #myInfo delgt
WHERE delgt.entity_ver_id = topLvl.entity_ver_id
AND delgt.req_id = reqLvl.req_id
AND delgt.resp_id = respLvl.resp_id
ORDER BY sort_seq_nbr
FOR XML PATH(''), type
)'response/delegator'
FROM #myInfo respLvl
WHERE respLvl.entity_ver_id = topLvl.entity_ver_id
AND respLvl.req_id = reqLvl.req_id
ORDER BY sort_seq_nbr
FOR XML PATH(''), type
)'request/responses'
FROM #myInfo reqLvl
WHERE reqLvl.entity_ver_id = topLvl.entity_ver_id
GROUP BY
req_id,
req_date,
amt,
priority_code,
Entr_by_pin,
entr_by_name,
Req_by_pin,
requestor_name,
[status],
status_date,
req_cmnt
ORDER BY MAX(sort_seq_nbr)
FOR XML PATH(''), TYPE
)'entity/requests'

FROM #myInfo topLvl
GROUP BY entity_ver_id,
ENTITY_CAT_ID,
client_id,
ver_name,
ver_desc,
create_date,
created_by_user_pin,
entity_ver_id,
entity_amt
ORDER BY MAX(sort_seq_nbr)
FOR XML PATH(''), TYPE
)'entities'
FROM (SELECT '900004' AS id, 'Request Accepted' AS [TEXT])msg
FOR XML PATH(''), ROOT('doc'), TYPE
)X(rslt);

SELECT @rslt AS Xml_Result

I think this is the thing that causes the confusion: sp_xml_preparedocument

So that's just saying that when you use the XML parser to open a document for use with OPENXML, up to one-eighth of SQL Server memory is potentially available to Msxmlsql.dll - should it need it.

This is the first MSFT answer I found with Google to back that up: MSDN Forums Thread.

My impression (though the method has so far escaped deprecation) is that XML DML methods/XQuery/XPath/FOR XML and the gang are to be preferred to using sp_xml_preparedocument and OPENXML wherever possible. There are many difficulties associated with OPENXML so personally I avoid it.

To be clear, all that applies specifically to prepare/remove document and OPENXML. XML variables, columns and so on allocate memory in the usual fashion. That applies to the XML portion of the query optimizer too. One interesting thing about xml variables (like all the MAX datatypes when used as variables) is that they require tempdb workspace.

Many of the XML features use streaming interfaces to minimize memory consumption - bulk loading XML is a good example of this. See XML Support in Microsoft SQL Server 2005

me again

Yes the wording could be better. I refreshed my memory on the detail last night (one of Ken Henderson's excellent books) so I just want to add:

SQL Server uses MSXML and the DOM to process documents you load via sp_xml_preparedocument. It restricts the virtual memory MSXML can use for DOM processing to one-eighth of the physical memory on the machine or 500MB, whichever is less.

It's unlikely that MSXML would be able to get 500MB of virtual memory since SQL Server reserves most of the user mode address space for use by the buffer cache. MSXML allocates memory via the multi-page allocator (the so-called MemToLeave region) which has a default maximum size of 256MB. Since that region is used for all sorts of stuff (by SQL Server as well) it's likely to be much less than that.

MSXML is limited to 500MB of virtual memory regardless of the amount of memory on the machine because SQL Server calls the GlobalMemoryStatus Win32 API function to determine the amount of available physical memory. On machines with more than 4GB of physical memory, GlobalMemoryStatus can return incorrect information, so Windows returns a -1 to indicate an overflow. The Win32 API function GlobalMemoryStatusEx exists to address this shortcoming, but SQLXML does not call it. Laugh

One last thing that I had forgotten - MSXML had an ordinal-only DLL entry point added specifically for SQL Server to allow it to set the maximum virtual memory to use.