none
Storing a TSQL query in XML

    Question


  • I am trying to use SQL Server 2005 Broker Services.  The messages that I'm trying to pass as well formed XML contain a TSQL query.

    For example, the message might contain a query like

    SELECT * FROM ATABLE where ACOLUMN < 20

    I get an error message when I try to build the XML message because of the "less than" operator

    DECLARE @WLQuery XML ;
    set @wlquery = convert(xml,' <aquery>select * from atable where acolumn < 20</aquery>')
    print convert(nvarchar,@wlquery)

    Msg 9455, Level 16, State 1, Line 2
    XML parsing: line 1, character 46, illegal qualified name character

    If I use the "greater than" operator, then I don't get an error message, but I get the XML code for this operator

    DECLARE @WLQuery XML ;
    set @wlquery = convert(xml,'<aquery>select * from atable where acolumn > 20</aquery>')
    print convert(nvarchar(1024),@wlquery)

    <aquery>select * from atable where acolumn &gt; 20</aquery>

    I understand that > and < are reserved in XML, and must be represented by the codes, so I understand how the > is being represented.

    How can I handle this?  I simply want to get the SQL query into the xml so it can be sent as a service broker message, and then have the stored procedure servicing the queue read the message, and execute the query.  There must be some way to handle the sql comparison operators.

    Thanks for any help.




    Thursday, June 21, 2007 2:05 AM

Answers

  • Insecurity:

     

    I am still new to XML and I do not know what is best.  You might be able to use a strategy of encoding your statements before you write them as XML and then decode them once you read them as XML.  In this example I am simply using the REPLACE function and performing these translations:

     

    • '<'   ==>   '`LT~'
    • '>'   ==>   '`GT~'
    • '&'  ==>   '`AMP~'

    For example:

     

    Code Snippet

    select replace(replace(replace(statement,'<','`LT~'),'<','`GT~'),'&','`AMP~') as statement
    from ( select 'select * from atable where acolumn < 20' as statement union all
           select 'select * from atable where acolumn > 20' union all
           select 'select * from atable where acolumn >= 20' union all
           select 'select * from atable where acolumn <= 20' union all
           select 'select * from atable where acolumn => 20' union all
           select 'select * from atable where acolumn <> 20' union all
           select 'select * from atable where acolumn != 20' union all
           select 'select * from atable where acolumn & 20 = 20' union all
           select 'select * from atable where acolumn | 20 = 20' union all
           select 'select * from atable where acolumn ^ 20 = 0' union all
           select 'select * from atable where acolumn % 20 = 2'
         ) as SQL
    for xml auto

     

    This will yield an XML string that can be decoded in a similar fashion with the REPLACE function:

     

    Code Snippet

    declare @x xml
    set @x =
    '<SQL statement="select * from atable where acolumn `LT~ 20"/><SQL statement="select * from atable where acolumn &gt; 20"/><SQL statement="select * from atable where acolumn &gt;= 20"/><SQL statement="select * from atable where acolumn `LT~= 20"/><SQL statement="select * from atable where acolumn =&gt; 20"/><SQL statement="select * from atable where acolumn `LT~&gt; 20"/><SQL statement="select * from atable where acolumn != 20"/><SQL statement="select * from atable where acolumn `AMP~ 20 = 20"/><SQL statement="select * from atable where acolumn | 20 = 20"/><SQL statement="select * from atable where acolumn ^ 20 = 0"/><SQL statement="select * from atable where acolumn % 20 = 2"/>'

     

    select replace(replace(replace(
              t.value (
    './@statement', 'varchar(100)'),
              '`LT~','<'),'`GT~','>'),'`AMP~','&')
           as statement
      from @x.nodes('/SQL') x(t)

     

    /*
    statement
    --------------------------------------------
    select * from atable where acolumn < 20
    select * from atable where acolumn > 20
    select * from atable where acolumn >= 20
    select * from atable where acolumn <= 20
    select * from atable where acolumn => 20
    select * from atable where acolumn <> 20
    select * from atable where acolumn != 20
    select * from atable where acolumn & 20 = 20
    select * from atable where acolumn | 20 = 20
    select * from atable where acolumn ^ 20 = 0
    select * from atable where acolumn % 20 = 2
    */

     

     

     

    Thursday, June 21, 2007 11:51 AM
    Moderator