InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 10:54:38 GMT20http://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspx[quote][b]Paul White (10/21/2009)[/b][hr][quote]...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[/quote]Can you give me a pointer to that, Paul? That's something that I would love to check out.Wed, 21 Oct 2009 17:57:32 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspx[quote]Could you kindly post the DDL for #myinfo and the test data you used for this article?[/quote]Sure. The data used to populate this table came from several tables, and our test environment was populated from our production environment, so I can't share that exact data here. But I've included a couple of insert statements which represent two responses for one entity/request so you can see the nesting in action. This creates and drops a temp table, and displays the xml results as both a string and as an xml data-type (results are best viewed in grid mode).[code="sql"]SET NOCOUNT onCREATE TABLE #myInfo( [amt] [money] NULL, [client_id] [nvarchar](50) NULL, [create_date] [datetime] NOT NULL, [created_by_user_pin] [varchar](30) NOT NULL, [email_addr] [varchar](100) NULL, [email_cmnt] [nvarchar](500) NULL, [email_first_opened] [datetime] NULL, [email_ind] [char](1) NOT NULL, [email_last_opened] [datetime] NULL, [email_last_sent_date] [datetime] NULL, [entity_cat_id] [int] NOT NULL, [entity_owner_email_first_opened_date] [datetime] NULL, [entity_owner_user_pin] [varchar](30) NULL, [entity_ver_id] [int] NOT NULL, [entr_by_pin] [varchar](30) NULL, [entr_date] [datetime] NOT NULL, [respondent_name] [nvarchar](4000) NULL, [nbr_times_sent] [int] NULL, [position_code] [varchar](1) NULL, [primary_seq_nbr] [int] NULL, [priority_code] [char](1) NOT NULL, [req_addl_resp_id] [int] NULL, [req_by_pin] [varchar](30) NOT NULL, [req_cmnt] [nvarchar](500) NULL, [req_date] [datetime] NOT NULL, [req_email_first_opened_date] [datetime] NULL, [req_email_sent_date] [datetime] NULL, [req_id] [int] NOT NULL, [req_route_type_id] [int] NULL, [req_source] [char](1) NULL, [req_type_id] [int] NOT NULL, [resp_cmnt] [ntext] NULL, [resp_date] [datetime] NULL, [resp_delegator_user_pin] [varchar](30) NULL, [resp_id] [int] NULL, [resp_status] [varchar](1) NULL, [resp_type] [char](1) NULL, [resp_user_pin] [varchar](30) NULL, [route_dtl_id] [int] NULL, [status] [char](1) NULL, [status_date] [datetime] NOT NULL, [tasklist_ind] [char](1) NULL, [urgent_cmnt] [nvarchar](500) NULL, [url] [nvarchar](500) NULL, [ver_desc] [nvarchar](500) NULL, [ver_name] [nvarchar](50) NOT NULL, [ver_nbr] [int] NOT NULL, [entity_amt] [money] NULL, [requestor_name] [nvarchar](4000) NULL, [delegator_name] [nvarchar](4000) NULL, [FINAL_APPROVER] [varchar](1) NOT NULL, [entr_by_name] [nvarchar](4000) NULL, sort_seq_nbr int) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]INSERT INTO #myInfo([amt],[client_id],[create_date],[created_by_user_pin],[email_addr],[email_cmnt],[email_first_opened],[email_ind],[email_last_opened],[email_last_sent_date],[entity_cat_id],[entity_owner_email_first_opened_date],[entity_owner_user_pin],[entity_ver_id],[entr_by_pin],[entr_date],[respondent_name],[nbr_times_sent],[position_code],[primary_seq_nbr],[priority_code],[req_addl_resp_id],[req_by_pin],[req_cmnt],[req_date],[req_email_first_opened_date],[req_email_sent_date],[req_id],[req_route_type_id],[req_source],[req_type_id],[resp_cmnt],[resp_date],[resp_delegator_user_pin],[resp_id],[resp_status],[resp_type],[resp_user_pin],[route_dtl_id],[status],[status_date],[tasklist_ind],[urgent_cmnt],[url],[ver_desc],[ver_name],[ver_nbr],[entity_amt],[requestor_name],[delegator_name],[FINAL_APPROVER],[entr_by_name],[sort_seq_nbr])VALUES (14.20, '1', CONVERT(DATETIME, 0x00009ca200cd5d6b), 'myPin', '', NULL, NULL, 'N', NULL, NULL, 1, NULL, 'myOwner', 29191, 'enterByPin', CONVERT(DATETIME, 0x00009ca200cd5dfd), 'John Doe', 0, '3', 2, 'M', -1, 'reqPin', NULL, CONVERT(DATETIME, 0x00009ca200cd5dfd), NULL, NULL, 1, 1, 'T', 4, NULL, CONVERT(DATETIME, 0x00009ca200cd5e27), 'dlgtrPin1', 39860, 'A', 'A', 'resPin1', 10, 'P', CONVERT(DATETIME, 0x00009ca200cd5dfd), 'Y', NULL, '', 'Description', 'Name', 1, 14.20, 'Requestor Name', 'Delegator Name1', 'N', 'enterBy Name', 1)INSERT INTO #myInfo([amt],[client_id],[create_date],[created_by_user_pin],[email_addr],[email_cmnt],[email_first_opened],[email_ind],[email_last_opened],[email_last_sent_date],[entity_cat_id],[entity_owner_email_first_opened_date],[entity_owner_user_pin],[entity_ver_id],[entr_by_pin],[entr_date],[respondent_name],[nbr_times_sent],[position_code],[primary_seq_nbr],[priority_code],[req_addl_resp_id],[req_by_pin],[req_cmnt],[req_date],[req_email_first_opened_date],[req_email_sent_date],[req_id],[req_route_type_id],[req_source],[req_type_id],[resp_cmnt],[resp_date],[resp_delegator_user_pin],[resp_id],[resp_status],[resp_type],[resp_user_pin],[route_dtl_id],[status],[status_date],[tasklist_ind],[urgent_cmnt],[url],[ver_desc],[ver_name],[ver_nbr],[entity_amt],[requestor_name],[delegator_name],[FINAL_APPROVER],[entr_by_name],[sort_seq_nbr])VALUES (14.20, '1', CONVERT(DATETIME, 0x00009ca200cd5d6b), 'myPin', '', NULL, NULL, 'N', NULL, NULL, 1, NULL, 'myOwner', 29191, 'enterByPin', CONVERT(DATETIME, 0x00009ca200cd5dfd), 'Jane Doe', 0, '3', 2, 'M', -1, 'reqPin', NULL, CONVERT(DATETIME, 0x00009ca200cd5dfd), NULL, NULL, 1, 1, 'T', 4, NULL, CONVERT(DATETIME, 0x00009ca200cd5e27), 'dlgtrPin2', 39858, 'P', 'A', 'resPin2', 10, 'P', CONVERT(DATETIME, 0x00009ca200cd5dfd), 'Y', NULL, '', 'Description', 'Name', 1, 14.20, 'Requestor Name', 'Delegator Name2', 'Y', 'enterBy Name', 2)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])msgFOR XML PATH(''), ROOT('doc'), TYPE)X(rslt);SELECT @rslt AS Xml_Result-- Output as xml type as well for testing:DECLARE @xml xmlSET @xml = @rsltSELECT @xmlDROP TABLE #myInfo[/code]Wed, 21 Oct 2009 11:53:27 GMTSteve McRoberts-357330http://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxCould you kindly post the DDL for #myinfo and the test data you used for this article?Wed, 21 Oct 2009 09:50:39 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxIts 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 altogetherWed, 21 Oct 2009 08:27:38 GMTRoland Howard Boormanhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspx[quote][b]irozenberg (10/18/2009)[/b][hr]Steve, Thank you for your article. I would like to add my 2 cents:- [b]xml[/b] 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 [b]schema[/b], each xml parameter in or out could have different schema);- there is an alternative to xml data type in SQL Server 2008 it called [b]Table-Valued Parameters[/b]. Cheers Regards from Down Under[/quote]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.Wed, 21 Oct 2009 07:34:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxThanks, 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?Wed, 21 Oct 2009 06:33:38 GMTSteve McRoberts-357330http://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxYes, 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"][size="2"][color="blue"]EXECUTE [/color][color="darkred"]sp_xml_preparedocument [/color][color="#434343"]@hdl [/color][color="black"]OUTPUT[/color][color="gray"], [/color][color="#434343"]@xml[/color][color="blue"]INSERT  INTO [/color][color="#434343"]@xmlEdgeTbl        [/color][color="blue"]SELECT  [/color][color="gray"]*        [/color][color="blue"]FROM    [/color][color="black"]OPENXML[/color][color="gray"]([/color][color="#434343"]@hdl[/color][color="gray"], [/color][color="red"]N'/doc'[/color][color="gray"])[/color][color="blue"]EXECUTE [/color][color="darkred"]sp_xml_removedocument [/color][color="#434343"]@hdl[/color][/size][/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.Wed, 21 Oct 2009 05:12:27 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxHey 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.PaulWed, 21 Oct 2009 02:01:20 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspx[quote][b]Anil-465177 (10/19/2009)[/b][hr]However, it consumes a lot of cpu cycles to read the xml data as SQL is not natively optimized for XML.[/quote]Nonsense. SQL Server contains many optimizations for generating and consuming XML. A [i]well-designed[/i] 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.[quote][b]Anil-465177 (10/19/2009)[/b][hr]We have high CPU usage under high volume transactions, causing slow response times.[/quote]No doubt. ;-)Wed, 21 Oct 2009 01:41:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspx[quote][b]Steve McRoberts-357330 (10/19/2009)[/b][hr]Please see the warning from Mauve, above, regarding its allocation of 1/8 of the available memory.[/quote]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...:-P2. (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 [url]http://www.sqlservercentral.com/Forums/Topic757986-640-1.aspx[/url]:[quote][b]me[/b][hr]I think this is the thing that causes the confusion: [u][url=http://msdn.microsoft.com/en-us/library/ms187367.aspx]sp_xml_preparedocument[/url][/u]So that's just saying that when you use the XML parser to open a document for use with OPENXML, [i]up to[/i] one-eighth of SQL Server memory is [i]potentially [/i]available to Msxmlsql.dll - [i]should it need it[/i].This is the first MSFT answer I found with Google to back that up: [u][url=http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/51b46347-adee-4ac2-9b73-d4cd31aa2793/]MSDN Forums Thread[/url][/u].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 [u][url=http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx#sql2k5xml_topic3]XML Support in Microsoft SQL Server 2005[/url][/u][/quote][quote][b]me again[/b][hr]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 [i]one-eighth of the physical memory on the machine or 500MB, whichever is less[/i].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 [i]regardless of the amount of memory on the machine[/i] 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.[/quote]Cheers,PaulWed, 21 Oct 2009 01:33:43 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxThe 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.Mon, 19 Oct 2009 12:38:04 GMTSteve McRoberts-357330http://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxYes, 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.Mon, 19 Oct 2009 11:15:36 GMTAnil-465177http://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxIt 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!Mon, 19 Oct 2009 08:37:23 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxWe'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 [url]http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx[/url]Mon, 19 Oct 2009 06:56:52 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxGreat article - thank you!Mon, 19 Oct 2009 05:59:13 GMTblandryhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxHi I have actually been doing this for years. But going much further because of my C++ backgroundBecause 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 RolandMon, 19 Oct 2009 04:53:21 GMTRoland Howard Boormanhttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxSteve, Thank you for your article. I would like to add my 2 cents:- [b]xml[/b] 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 [b]schema[/b], each xml parameter in or out could have different schema);- there is an alternative to xml data type in SQL Server 2008 it called [b]Table-Valued Parameters[/b]. Cheers Regards from Down UnderSun, 18 Oct 2009 23:19:30 GMTirozenberghttp://www.sqlservercentral.com/Forums/Topic800425-1664-1.aspxComments posted to this topic are about the item [url=/articles/XML/68056/]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_nbrWhere ~ matches the contents of the ORDER BY clause exactly. This gives us a sequentially numbered column in our sorted order.)[code="sql"]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])msgFOR XML PATH(''), ROOT('doc'), TYPE)X(rslt);SELECT @rslt AS Xml_Result[/code]Thu, 08 Oct 2009 16:57:09 GMTSteve McRoberts-357330