Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using XML to pass and return values to procs


Using XML to pass and return values to procs

Author
Message
Steve McRoberts-357330
Steve McRoberts-357330
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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                     
                  Wink'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
               Wink'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               
         Wink'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               
   Wink'entities'               
FROM (SELECT '900004' AS id, 'Request Accepted' AS [TEXT])msg
FOR XML PATH(''), ROOT('doc'), TYPE
)X(rslt);

SELECT @rslt AS Xml_Result


irozenberg
irozenberg
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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
Roland Howard Boorman
Roland Howard Boorman
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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
blandry
blandry
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 723
Great article - thank you!

There's no such thing as dumb questions, only poorly thought-out answers...
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1244 Visits: 2049
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.
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 2937
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
Anil-465177
Anil-465177
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Steve McRoberts-357330
Steve McRoberts-357330
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 11350
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...:-P

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. 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.


Cheers,

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search