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
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11240 Visits: 11354
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



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Phil Factor
Phil Factor
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 2953
Yes, I enjoyed the article too. It has really got me thinking.
Is there an alternative to getting the 'Edge table' other than ...
EXECUTE sp_xml_preparedocument @hdl OUTPUT, @xml
INSERT  INTO @xmlEdgeTbl
        
SELECT  *
        
FROM    OPENXML(@hdl, N'/doc')
EXECUTE sp_xml_removedocument @hdl

...?
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
Steve McRoberts-357330
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 82
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
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16127 Visits: 9729
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
Roland Howard Boorman
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 45
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9867 Visits: 14376
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
Steve McRoberts-357330
Steve McRoberts-357330
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 82
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,
[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])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


RBarryYoung
RBarryYoung
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: 10162 Visits: 9517
Paul White (10/21/2009)
[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

Can you give me a pointer to that, Paul? That's something that I would love to check out.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
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