Using XML to pass and return values to procs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Steve McRoberts » Using XML to pass and return values to procs

18 posts, Page 2 of 2

««1 2

Using XML to pass and return values to procs

Rate Topic

Display Mode

Topic Options

Author

Message

Paul White

Posted Wednesday, October 21, 2009 2:01 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990, Visits: 10,576

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

Phil Factor

Posted Wednesday, October 21, 2009 5:12 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:10 AM
Points: 535, Visits: 2,295

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

Steve McRoberts-357330

Posted Wednesday, October 21, 2009 6:33 AM

Forum 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

GSquared

Posted Wednesday, October 21, 2009 7:34 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

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

Roland Howard Boorman

Posted Wednesday, October 21, 2009 8:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 30, 2010 3:50 AM
Points: 3, Visits: 21

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

opc.three

Posted Wednesday, October 21, 2009 9:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #806623

Steve McRoberts-357330

Posted Wednesday, October 21, 2009 11:53 AM

Forum 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

RBarryYoung

Posted Wednesday, October 21, 2009 5:57 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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... Tongue

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 »

18 posts, Page 2 of 2

««1 2

Permissions