none
Adding a node to XML stored as a text column

    Question

  • I have a table which has the following structure:

    CREATE

     

    TABLE [Configuration].[t_Configurations](
    [Settings] [text] NOT NULL,
    [Machine] [varchar](128) NOT NULL,
    [Application] [varchar](128) NOT NULL,
    [User] [varchar](128) NOT NULL,
    [Module] [varchar](128) NOT NULL,
    [ConfigurationID] [int] IDENTITY(1,1) NOT NULL
    )

    The settings column contains text which looks like:
    <Settings>
     <item key="ClientSetting">
      <item key="Servers">
       <item key="Server1">
        <item key="Preference" value="1" />
        <item key="Url" value=http://someuri />
        <item key="UserID" value="UserIDX" />
        <item key="Password" value="XXXXX" />
        <item key="ServiceID" value="Service.WorkstationService" />
        <item key="ConnectTimeoutMS" value="5000" />
        <item key="NumConnectAttempts" value="3" />
        <item key="ReconnectIntervalMS" value="500" />
       </item>
      </item>
     </item>
    </Settings>

    I want to run a sql query to update the table for specific rows, in the rows that satisfy my criteria I want to add the node under <item key="Server1" >

    which is
    <item key="InfiniteRetry" value="true" />

    I thought I'd be able to do something like
    UPDATE t_Configurations
    SET [Setting] = [Setting].insert <item key="InfiniteRetry" value="true" /> after (some xpath query)
    WHERE Module = 'ClientTestSettings'
    but you can't operate on text columns as XML

    What would be the best way to approach this problem?

    Thursday, July 02, 2009 6:57 PM

Answers

  • You have three choices here:

    If you're using SQL Server 2005 and above you should really alter the column to the varchar(max) data type. I'm assuming that you have a column of type TEXT and SQL Server 2005 or 2008. If you're going to treat the text as XML in general, it would be better to alter the column to the XML data type.

    1. Consider the XML as a TEXT column and use a series of calls to TEXTPTR, READTEXT, and WRITETEXT to update it.

    2. CAST or CONVERT the TEXT column to VARCHAR(MAX) and use a series of calls to the WRITE method (on varchar(max)) to update it.

    3. CAST or CONVERT the TEXT column to XML, use the XML modify method to insert the node, and convert back to TEXT to store it back. But because you can't cast/convert XML data type to TEXT directly, you first must convert it to VARCHAR(MAX). With a variable of type XML, it would look like this:

    declare @x xml
    -- initialize
    -- invoke modify method to add node, then
    select CONVERT(xml, CONVERT(varchar(max), @x))

    Altering the column to XML would be preferred because, in your UPDATE statement, you'll need to convert, modify, and convert back in one statement. TEXT can be converted to XML directly, but XML cannot be directly converted to TEXT. Bear in mind that if you alter the column to the XML data type, all the rows must contain well-formed XML or NULL.

    Hope this helps,
    Cheers,
    Bob Beauchemin
    SQLskills
    • Marked as answer by bpeikes Thursday, July 02, 2009 7:29 PM
    Thursday, July 02, 2009 7:27 PM

All replies

  • You have three choices here:

    If you're using SQL Server 2005 and above you should really alter the column to the varchar(max) data type. I'm assuming that you have a column of type TEXT and SQL Server 2005 or 2008. If you're going to treat the text as XML in general, it would be better to alter the column to the XML data type.

    1. Consider the XML as a TEXT column and use a series of calls to TEXTPTR, READTEXT, and WRITETEXT to update it.

    2. CAST or CONVERT the TEXT column to VARCHAR(MAX) and use a series of calls to the WRITE method (on varchar(max)) to update it.

    3. CAST or CONVERT the TEXT column to XML, use the XML modify method to insert the node, and convert back to TEXT to store it back. But because you can't cast/convert XML data type to TEXT directly, you first must convert it to VARCHAR(MAX). With a variable of type XML, it would look like this:

    declare @x xml
    -- initialize
    -- invoke modify method to add node, then
    select CONVERT(xml, CONVERT(varchar(max), @x))

    Altering the column to XML would be preferred because, in your UPDATE statement, you'll need to convert, modify, and convert back in one statement. TEXT can be converted to XML directly, but XML cannot be directly converted to TEXT. Bear in mind that if you alter the column to the XML data type, all the rows must contain well-formed XML or NULL.

    Hope this helps,
    Cheers,
    Bob Beauchemin
    SQLskills
    • Marked as answer by bpeikes Thursday, July 02, 2009 7:29 PM
    Thursday, July 02, 2009 7:27 PM
  • If I change the column to VARCHAR(max) could I use a "simple"  UPDATE statement?

    What would it look like?
    Thursday, July 02, 2009 7:57 PM
  • If you converted to varchar(max) and wanted to update a specific row and knew where the insertion point is in characters, you could use the T-SQL STUFF function like this:

    create table foo (id int, thecol varchar(max))

    insert foo values(1, REPLICATE(convert(VARCHAR(MAX), 'a'),10000) + 'This is a string')

    -- I want the insertion point to be postition 10011
    update foo set thecol = STUFF(thecol, 10011, 0, 'longer ')
     where id = 1
     
    -- reads 'this is a longer string'
    select SUBSTRING(thecol, 10001, 100)
    from foo

    If you don't know the exact insertion point or you have different rows with different insertion points, you're simply using more T-SQL string handling functions. You need to be careful with some of the string handling functions (like replicate) to cast/convert some of the operands to varchar(max).

    Cheers,
    Bob Beauchemin
    SQLskills

    Thursday, July 02, 2009 8:42 PM
  • I don't want to use substring function I want to use built in XML functions.
    i.e.
    SELECT CAST([Settings] as XML).Modify('Insert <item key="newkey" value="newvalue" /> INTO (/Settings)
    Thursday, July 02, 2009 8:45 PM
  • I don't think that syntactically you'll be able to do the cast/convert, update, and recast in a single statement. For one row you could use a construct like the one below. For multiple rows, you'll need to open an updatable cursor over the set.

    create table foo (id int, thecol xml)
    insert into foo values(1, '<foo/>')

    declare @x xml
    set @x = (select CONVERT(xml, thecol) from foo where id = 1) -- parens required
    set @x.modify('insert <bar/> into /foo[1] ')
    update foo set thecol = CONVERT(varchar(max), @x) where id = 1

    Unless there's some syntactic trick I'm missing, this form

    update foo
    set thecol = CONVERT(varchar(max), convert(xml, thecol).modify('insert <bar/> into /foo[1] '))
    where id = 1

    or even this

    declare @x xml
    set @x = (select CONVERT(xml, thecol) from foo where id = 1).modify('insert <bar/> into /foo[1] ')

    produce the error:

    Msg 8137, Level 16, State 1, Line 1
    Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

    Cheers,
    Bob Beauchemin
    SQLskills

    Thursday, July 02, 2009 9:30 PM
  • Since modify() is a mutator that does not return a result, you cannot inline it with a select. You need a transform, that you can model either with XQuery or FOR XML.

    For your example above, the following shows the FOR XML approach to do the transform.

    declare @xvc nvarchar(max) = N'<Settings>
     <item key="ClientSetting">
      <item key="Servers">
       <item key="Server0">
        <item key="Preference" value="1" />
       </item>
       <item key="Server1">
        <item key="Preference" value="1" />
        <item key="Url" value="http://someuri" />
        <item key="UserID" value="UserIDX" />
        <item key="Password" value="XXXXX" />
        <item key="ServiceID" value="Service.WorkstationService" />
        <item key="ConnectTimeoutMS" value="5000" />
        <item key="NumConnectAttempts" value="3" />
        <item key="ReconnectIntervalMS" value="500" />
       </item>
       <item key="Server2">
        <item key="Preference" value="1" />
       </item>
      </item>
     </item>
    </Settings>';

    set @xvc =  convert(nvarchar(max), (
                 select x.query('/Settings/item[.<<(/Settings/item[@key="ClientSetting"])[1]]')
                      , (select 'ClientSetting' as "@key"
                              , x.query('/Settings/item[@key="ClientSetting"]/item[.<<(/Settings/item[@key="ClientSetting"]/item[@key="Servers"])[1]]')
                              , (select 'Servers' as "@key"
                                      ,  x.query('/Settings/item[@key="ClientSetting"]/item[@key="Servers"]/item[.<<(/Settings/item[@key="ClientSetting"]/item[@key="Servers"]/item[@key="Server1"])[1]]')
                                      , (select 'Server1' as "@key"
                                              , (select CAST('<item key="InfiniteRetry" value="true" />' as XML))
                                              , x.query('/Settings/item[@key="ClientSetting"]/item[@key="Servers"]/item[@key="Server1"]/node()')
                                         for xml path('item'), type)
                                      , x.query('/Settings/item[@key="ClientSetting"]/item[@key="Servers"]/item[.>>(/Settings/item[@key="ClientSetting"]/item[@key="Servers"]/item[@key="Server1"])[1]]')
                                 for xml path('item'), type)
                              , x.query('/Settings/item[@key="ClientSetting"]/item[.>>(/Settings/item[@key="ClientSetting"]/item[@key="Servers"])[1]]')
                          for xml path('item'), type)
                      , x.query('/Settings/item[.>>(/Settings/item[@key="ClientSetting"])[1]]')
                  from (select cast(@xvc as xml)) T(x)
                  for xml path('Settings'), type)
                );
                     
    select @xvc;

    Cheers
    Michael


    -- Michael Rys
    Tuesday, July 21, 2009 8:39 PM