Post reply

Adding a soap header and footer to an FOR XML SELECT statement

  • February 24, 2010 at 10:35 pm

    #217113

    Hi

    How can I wrap XML transactions (see #Address below) with a soap envelope/header and footer?

    Also is there a way of naming the ROOT in the FOR XML clause ie t1:transactions?

    --soap envelope header:

    '<SOAP-ENV:Envelope

    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

    SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

    <SOAP-ENV:Header>

    <ns1:AuthenticationInfo xmlns:ns1="urn:thisNamespace">

    <ns1:UserName>John Doe</ns1:UserName>

    </ns1:AuthenticationInfo>

    </SOAP-ENV:Header>

    <SOAP-ENV:Body>'

    --soap footer:

    ' </SOAP-ENV:Body>

    </SOAP-ENV:Envelope>/

    DROP TABLE #Address

    CREATE TABLE #Address ([AddressID] [int] IDENTITY(1,1) NOT NULL,

    [AddressLine1] [nvarchar](60) NOT NULL,

    [AddressLine2] [nvarchar](60) NULL,

    [City] [nvarchar](30) NOT NULL)

    INSERT INTO #Address ([AddressLine1] ,[AddressLine2], [City]) SELECT '1970 Napa Ct', NULL, 'Bothell'

    INSERT INTO #Address ([AddressLine1] ,[AddressLine2], [City]) SELECT '9539 Glenside Dr', NULL, 'Bothell'

    INSERT INTO #Address ([AddressLine1] ,[AddressLine2], [City]) SELECT '636 Vine Hill Way', 'Unit 63b', 'Portland'

    INSERT INTO #Address ([AddressLine1] ,[AddressLine2], [City]) SELECT '6657 Sand Pointe Lane', 'Suite 2501', 'Seattle'

    INSERT INTO #Address ([AddressLine1] ,[AddressLine2], [City]) SELECT '80 Sunview Terrace', NULL, 'Duluth'

    --SELECT * FROM #Address

    SELECT

    [AddressLine1]AS [Address/Address1]

    ,[AddressLine2]AS [Address/Address2]

    ,[City]AS [City]

    FROM

    #Address

    FOR XML PATH('Addresses'), ROOT('transactions')

    Many thanks

    pYak

  • February 25, 2010 at 2:08 pm

    #1125212

    change your SELECT part to the following code:

    DECLARE @soapHeader VARCHAR(1000)

    DECLARE @soapFooter VARCHAR(1000)

    SET @soapHeader='<SOAP-ENV:Envelope

    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

    SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

    <SOAP-ENV:Header>

    <ns1:AuthenticationInfo xmlns:ns1="urn:thisNamespace">

    <ns1:UserName>John Doe</ns1:UserName>

    </ns1:AuthenticationInfo>

    </SOAP-ENV:Header>

    <SOAP-ENV:Body>

    '

    --soap footer:

    SET @soapFooter=' </SOAP-ENV:Body>

    </SOAP-ENV:Envelope>'

    SELECT CAST(@soapHeader+(SELECT

    [AddressLine1] AS [Address/Address1]

    , [AddressLine2] AS [Address/Address2]

    , [City] AS [City]

    FROM

    #Address

    FOR XML PATH('Addresses'), ROOT('transactions'))+@soapFooter AS XML)

    Regarding your request to change "the ROOT in the FOR XML clause to ie t1:transactions":

    I don't really think it's possible to do it within the SELECT FOR XML PATH section since you'd have to use WITH XMLNAMESPACES () syntax which would prevent doing the nested string concatenation.

    I haven't found a way yet to assign the result of that NAMESPACES statement into any kind of variable...

    So, I'd recommend to use ROOT('transactions'), add the namespace declaration for t1 to the @soapHeader variable and do a string replacement while concatenating the three substrings.

    Maybe there's an easier way to do it but I can't find it right now.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 25, 2010 at 2:18 pm

    #1125217

    Hi Lutz

    Thank you so much for your help!

    I was hoping to construct the xml inline in a view

    but it appears that I will need to use a stored proc now

    to acheive the result that I want.

    Thanks again

    Cheers

    pYak

  • February 25, 2010 at 2:25 pm

    #1125221

    I don't really understand why you'd need a view containing just one row...

    but the following statement worked fine on my system (after changing the temp table to a permanet table, of course...):

    CREATE VIEW test AS

    SELECT CAST('<SOAP-ENV:Envelope

    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

    SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

    <SOAP-ENV:Header>

    <ns1:AuthenticationInfo xmlns:ns1="urn:thisNamespace">

    <ns1:UserName>John Doe</ns1:UserName>

    </ns1:AuthenticationInfo>

    </SOAP-ENV:Header>

    <SOAP-ENV:Body>

    '+(SELECT

    [AddressLine1] AS [Address/Address1]

    , [AddressLine2] AS [Address/Address2]

    , [City] AS [City]

    FROM

    Address12

    FOR XML PATH('Addresses'), ROOT('transactions'))+' </SOAP-ENV:Body>

    </SOAP-ENV:Envelope>' AS XML) AS RESULT


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 25, 2010 at 2:50 pm

    #1125238

    Hi Lutz

    Thanks again.

    I didn't explain very well.

    I intended the view to return the same resultset as your 1st post.

    I guess the simple logic would be something like

    SELECT

    CAST(header + body + footer AS XML)

    FROM

    (

    SELECT

    '<Header><user>joe</user></Header>' as header

    ,( SELECT *

    FROM #Address

    FOR XML PATH('transaction')

    ) AS body

    , '<footer></footer>' AS footer

    FROM tally

    WHERE n= 1

    ) n

    Cheers

    pYak

  • February 25, 2010 at 3:13 pm

    #1125250

    As far as I can see, my first code snippet and the view will return the same data. What's different?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 25, 2010 at 3:40 pm

    #1125266

    Hi Lutz

    Sorry... my bad. That was a matter of shot then aim.

    You are spot on with the view.

    Thanks again for your help.

    Cheers

    pYak

  • February 25, 2010 at 3:48 pm

    #1125271

    Glad I could help 😀


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply