Using XML to pass and return values to procs

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    Comments posted to this topic are about the item Using XML to pass and return values to procs[/url]

    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

  • irozenberg

    SSC-Addicted

    Points: 478

    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

    SSC-Addicted

    Points: 408

    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

    SSCarpal Tunnel

    Points: 4761

    Great article - thank you!

    There's no such thing as dumb questions, only poorly thought-out answers...
  • Mauve

    SSChampion

    Points: 11316

    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


    [font="Arial Narrow"](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.[/font]

  • Phil Factor

    SSCoach

    Points: 19888

    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

    SSC Rookie

    Points: 35

    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

    Old Hand

    Points: 349

    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

    SSC Guru

    Points: 150442

    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[/url].

    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

    SSC Guru

    Points: 150442

    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

    SSC Guru

    Points: 150442

    Hey Steve,

    I just want to say how much I enjoyed reading your article today (even if I don't necessarily agree with all the content!)

    I have the greatest admiration for anyone that can dedicate so much personal time and effort to produce such an interesting and well-presented article, with the obvious intent of sharing a creative and thought-provoking idea with the masses.

    So, I just wanted to say "well done", and don't pay too much attention to the 'drive-by' posters.

    Paul

  • Phil Factor

    SSCoach

    Points: 19888

    Yes, I enjoyed the article too. It has really got me thinking.

    Is there an alternative to getting the 'Edge table' other than ...

    [font="Courier New"]EXECUTE sp_xml_preparedocument @hdl OUTPUT, @xml

    INSERT  INTO @xmlEdgeTbl

            SELECT  *

            FROM    OPENXML(@hdl, N'/doc')

    EXECUTE sp_xml_removedocument @hdl

    [/font]

    ...?

    If it is only in the region of 6 Ms and isn't a memory hog, then it looks like a good way of doing it.

    Best wishes,
    Phil Factor
    Simple Talk

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    Thanks, Paul; you made my day!

    Phil: I also started looking for a replacement for openXml. One suggestion was to use nodes, but in our system we have a variety of xml formats coming in for various requests, and they have different tags. The nice thing about openxml is that you don't need to know the tag names in your xml ahead of time; it gives them to you in the edge table. So, I second Phil's question: Does anyone know of a better alternative to producing the edge table?

  • GSquared

    SSC Guru

    Points: 260824

    irozenberg (10/18/2009)


    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

    Table-value parameters miss out on a number of the XML advantages. Flexible data structure is one of them. Also, web services are pretty much ubiquitous these days, and the XML for those fits right into the pattern in the article of accepting and returning XML. That's got all the usual consistency advantages.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Roland Howard Boorman

    SSC-Addicted

    Points: 408

    Its an interesting chat.

    My use of XML here has been to actually enable the Server to apply various or even alternative business rules logic to an Application even to the extent of changing event handling. As I say the XML can be used as a object stream and interface with back end relational data.

    Another use is actually to cut out the need for a SSIS layer altogether

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply