Converting a cookie to XML

Question

Post reply

Converting a cookie to XML

Andy Krafft

SSC Enthusiast

Points: 109
More actions
September 13, 2007 at 5:43 am

#374290

Hi
Can anyone propose an efficient way to convert a cookie string such as:
id=37&pos=down&sess=12345
to XML such as:
<Root>
    <cookie name="id">37</cookie>
    <cookie name="pos">down</cookie>
    <cookie name="sess">12345</cookie>
</Root>
or:
<Root>
    <id>37</id>
    <pos>down</pos>
    <sess>12345</sess>
</Root>
I am trying to process a log file from a web server and store the cookie field as an XML column. I've tried FOR XML but cannot work out (without using dynamic SQL) how to get the variable number of cookies per string and dynamic cookie names into the XML format.
Thanks
Andy

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

Hi Andy,

You could use a function like:

CREATE FUNCTION f ( @a VARCHAR(2000) )
RETURNS xml
AS BEGIN
    DECLARE @res1 varchar(2000)
    SET @res1 = ''
    SET @a = @a + '&'
    WHILE ( CHARINDEX('&', @a) > 0 )
        BEGIN
            SELECT  @res1 = @res1 + ( SELECT    LTRIM(RTRIM(LEFT(SUBSTRING(@a, 1, CHARINDEX('&', @a) - 1),
                                                         CHARINDEX('=', @a)
                                                         - 1))) AS "cookie/@Name"
                                      , LTRIM(RTRIM(RIGHT(SUBSTRING(@a, 1, CHARINDEX('&', @a) - 1),
                                                          CHARINDEX('=', @a)))) AS "cookie"
                              FOR XML PATH('')
                            )
            SET @a = SUBSTRING(@a, CHARINDEX('&', @a) + 1, LEN(@a))
        END
    RETURN '<Root>' + @res1 + '</Root>'
   END

(no error handling, ...)

However, in such cases using CLR would probably be justified.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

This does indeed seem to be a case where a CLR function could be appropriate. There aren't many such situations, IMHO.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Andy Krafft SSC Enthusiast Points: 109 More actions · Answer 3

Andras

Thanks for very much -- that's a life saver!

I'm trying to use it in a computed column: do you know why it comes up with "Computed column 'cookies' in table 'Tmp_Pages' cannot be persisted because the column is non-deterministic" when I try and alter the table? It seems to me that your function is most definately deterministic and so I need a way to "educate" SQL!

Thanks.

Andy

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Hmm, it would seem that all of the string-based builtin functions called by the user defined function you have are deterministic. You can verify against the list in BOL. If this is the case then perhaps something related to the 'XMLness' of the function is causing it to be nondeterministic.

Note that if you rewrite the function as CLR YOU get to specify whether or not it is deterministic via the SqlFunction custom attribute. See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/58e95e8f-39be-4290-94c8-2f1afce25615.htm in BOL.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 5

SQL Server is actually right here. While we know that we are processing the first parts of the string, the select @x=@x+ ... is not a very nice thing to use in SQL, and I'm sure it confuses SQL Server, especially as there is no ordering it could use to disambiguate.

Rewriting this in CLR is a viable solution.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software