none
Xml modify issue when namespace is involved

    Question

  • Hi all. I'm working with:

    Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31  

    Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1) 

    I'm trying to call the xml modify method and get the WidgetID updated to be 5.

    Without a namespace involved, it's fine.. the first WidgetID node gets updated:

     

    declare @x xml, @CurrentWidgetID int
    select @x = '<Page><Widgets>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w1</Name>
        </PageWidget>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w2</Name>
        </PageWidget>
      </Widgets>
    </Page>', @CurrentWidgetID = 5
    
    set @x.modify('replace value of (/Page/Widgets/PageWidget/WidgetID[text()=0]/text())[1] with sql:variable("@CurrentWidgetID")')
    select @x
    


     

    But adding the namespace I'm dealing with in the data and I get odd errors.

     

    Incorrect syntax near the keyword 'set'. I have tried on this for a long time now and can't get it resolved. Maybe someone can help strengthen my xml/sql knowledge even more as to why there's an issue?

     

     

    declare @x xml, @CurrentWidgetID int
    select @x = '<Page xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/test">
    <Widgets>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w1</Name>
        </PageWidget>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w2</Name>
        </PageWidget>
      </Widgets>
    </Page>', @CurrentWidgetID = 5
    
    ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/test')
    set @x.modify('replace value of (/Page/Widgets/PageWidget/WidgetID[text()=0]/text())[1] with sql:variable("@CurrentWidgetID")')
    
    select @x
    


     

     


    • Edited by ryanb2009 Monday, January 30, 2012 3:46 PM updating 2nd xml w/ a namepsace
    Friday, January 27, 2012 11:18 PM

Answers

  • Interesting.  I had never tried this. By the way, in your xml you do not have a default namespace declaration, so your intent will not work.

    In SQL Server 2008 R2 (and perhaps 2005, I don't have a server set up), you could do the following:

    declare @x xml, @CurrentWidgetID int
    select @x = '<Page xmlns="http://schemas.datacontract.org/2004/07/test">
    <Widgets>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w1</Name>
        </PageWidget>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w2</Name>
        </PageWidget>
      </Widgets>
    </Page>', @CurrentWidgetID = 5
    
    set @x.modify('
     declare default element namespace "http://schemas.datacontract.org/2004/07/test";
    replace value of (/Page/Widgets/PageWidget/WidgetID[text()=0]/text())[1] with sql:variable("@CurrentWidgetID")')
    
    select @x
    

     


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by ryanb2009 Monday, January 30, 2012 3:48 PM
    Saturday, January 28, 2012 3:17 AM

All replies

  • Interesting.  I had never tried this. By the way, in your xml you do not have a default namespace declaration, so your intent will not work.

    In SQL Server 2008 R2 (and perhaps 2005, I don't have a server set up), you could do the following:

    declare @x xml, @CurrentWidgetID int
    select @x = '<Page xmlns="http://schemas.datacontract.org/2004/07/test">
    <Widgets>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w1</Name>
        </PageWidget>
        <PageWidget>
          <WidgetID>0</WidgetID>
          <Name>w2</Name>
        </PageWidget>
      </Widgets>
    </Page>', @CurrentWidgetID = 5
    
    set @x.modify('
     declare default element namespace "http://schemas.datacontract.org/2004/07/test";
    replace value of (/Page/Widgets/PageWidget/WidgetID[text()=0]/text())[1] with sql:variable("@CurrentWidgetID")')
    
    select @x
    

     


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by ryanb2009 Monday, January 30, 2012 3:48 PM
    Saturday, January 28, 2012 3:17 AM
  • Whoops - updated the 2nd xml to have the namespace.

    Your suggestion works for 2005 too. Thanks a million for getting me over this hump.

    Monday, January 30, 2012 3:49 PM