Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using XML to pass and return values to procs Expand / Collapse
Author
Message
Posted Thursday, October 08, 2009 4:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, Visits: 82
Comments posted to this topic are about the item Using XML to pass and return values to procs

Note that in the article the query to return the xml in "highly nested" format is missing the actual FROM clauses that will cause it to nest properly. As-is, each new response or request would generate a new entity parent.

Below is the SQL to nest properly (so multiple requests and responses for the same entity appear under that one entity).

Because the denormalized temp table (which acts as the source for this query) will have been previously sorted in a variety of ways, I added a sort_seq_nbr column to it, and then I have this final query order by the sort_seq_nbr (otherwise the FOR XML will re-order the data in its own way).

(Adding the sort_seq_nbr to the temp table uses another new feature of SQL 2005: we define the column as:
ROW_NUMBER() over(order by ~) as sort_seq_nbr
Where ~ matches the contents of the ORDER BY clause exactly. This gives us a sequentially numbered column in our sorted order.)

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

Post #800425
Posted Sunday, October 18, 2009 11:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 8:44 PM
Points: 30, Visits: 145
Steve,

Thank you for your article. I would like to add my 2 cents:
- xml data-type should be and must be strongly-typed (i.e. client has to pass not just well-formed XML but also XML with a particular schema, each xml parameter in or out could have different schema);
- there is an alternative to xml data type in SQL Server 2008 it called Table-Valued Parameters.

Cheers
Regards from Down Under
Post #804899
Posted Monday, October 19, 2009 4:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
Hi
I have actually been doing this for years.
But going much further because of my C++ background
Because XML will integrate better with classes including apparent inheritance.
Controling front end business logic by controls status and even alternative front end event handling.
The idea is to build the business logic into to back end database.
What is excellent using XML in this way is actually to breakaway from the Relational constraints (hide this) and treat the data Source as Object streams.
Its very powerful and for me proven. The idea is to send and pass XML to and from. Forming the XML at the server end requires work but the result is awesome because it is as if the Server was an Object Database.

Best regards
Roland
Post #804962
Posted Monday, October 19, 2009 5:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 07, 2012 9:23 AM
Points: 304, Visits: 716
Great article - thank you!


There's no such thing as dumb questions, only poorly thought-out answers...
Post #804982
Posted Monday, October 19, 2009 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:53 AM
Points: 1,182, Visits: 1,949
We've successfully been using XML as a data transport mechanism with our .Net web application. However, things are a bit cleaner in that we can pass native XML (datatype XML) back and forth without having to convert to NVARCHAR.

One comment: Your use of OPENXML in a web application is putting a lot of pressure on your system.

See: Stop Using OPENXML
http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #805001
Posted Monday, October 19, 2009 8:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 561, Visits: 2,416
It is an interesting twist on a popular technique. The only point where I was a bit doubtful was to use EXECUTE sp_xml_preparedocument to get the edge table. Do you have any test data on the time it takes and the memory overhead (Temporary, I know!). It is possible to do this in TSQL, though not at all pleasant!


Best wishes,

Phil Factor
Simple Talk
Post #805090
Posted Monday, October 19, 2009 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 3:00 AM
Points: 1, Visits: 91
Yes, it comes with a lot of flexibility for smaller xml documents. However, it consumes a lot of cpu cycles to read the xml data as SQL is not natively optimized for XML.
We have high CPU usage under high volume transactions, causing slow response times.
Post #805197
Posted Monday, October 19, 2009 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, Visits: 82
The xml that is input to this is usually small: a couple of dozen lines. It takes 6 milliseconds to populate the @xmlEdgeTbl via the openxml.
When I try this with 2,607 lines of xml (more than we would ever process) it takes 474 milliseconds. These are acceptable times for our purposes.
Please see the warning from Mauve, above, regarding its allocation of 1/8 of the available memory.
Post #805231
Posted Wednesday, October 21, 2009 1:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 11,168, Visits: 10,932
Steve McRoberts-357330 (10/19/2009)
Please see the warning from Mauve, above, regarding its allocation of 1/8 of the available memory.

Two points, one quick, and one not so quick:

1. (The quick one) Service Broker uses OPENXML internally, not nodes(). I am not a fan of OPENXML for many reasons, but I just want to point out that it gets a bad press in general. If it's good enough for Service Broker...

2. (The long one) OPENXML certainly does not automatically allocate one eighth of server memory. The referenced MS article is extremely badly worded. Let me reproduce the answers I gave a few months ago at http://www.sqlservercentral.com/Forums/Topic757986-640-1.aspx:

me

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.

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.


Cheers,

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #806296
Posted Wednesday, October 21, 2009 1:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 11,168, Visits: 10,932
Anil-465177 (10/19/2009)
However, it consumes a lot of cpu cycles to read the xml data as SQL is not natively optimized for XML.

Nonsense. SQL Server contains many optimizations for generating and consuming XML. A well-designed system can process XML extremely efficiently. I seem to keep mentioning Service Broker recently, but it is an excellent example of how well an XML-message-based system can scale out and up.

Anil-465177 (10/19/2009)
We have high CPU usage under high volume transactions, causing slow response times.

No doubt.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #806300
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse