Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using XML to pass and return values to procs Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 2:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #806308
Posted Wednesday, October 21, 2009 5:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 579, Visits: 2,519
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
Post #806378
Posted Wednesday, October 21, 2009 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, 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?
Post #806423
Posted Wednesday, October 21, 2009 7:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #806487
Posted Wednesday, October 21, 2009 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, 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
Post #806535
Posted Wednesday, October 21, 2009 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #806623
Posted Wednesday, October 21, 2009 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, 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


Post #806691
Posted Wednesday, October 21, 2009 5:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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...

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."
Post #806898
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse