October 21, 2009 at 9:50 am
Could you kindly post the DDL for #myinfo and the test data you used for this article?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 21, 2009 at 11:53 am
Could you kindly post the DDL for #myinfo and the test data you used for this article?
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).
SET NOCOUNT on
CREATE 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,
[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],,[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],,[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])msg
FOR XML PATH(''), ROOT('doc'), TYPE
)X(rslt);
SELECT @rslt AS Xml_Result
-- Output as xml type as well for testing:
DECLARE @xml xml
SET @xml = @rslt
SELECT @xml
DROP TABLE #myInfo
October 21, 2009 at 5:57 pm
Paul White (10/21/2009)
...
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
Can you give me a pointer to that, Paul? That's something that I would love to check out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply