locked
Updating Xml node in a xmltype column RRS feed

  • Question

  • I have a table with xml datatype column PartnerResponse

    In it i have a data

    <mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"></mailxml:PartnerApptQueryResponse>

     

    I wants to update the node

    xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"

    into

    xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"

    How can i do it from sql?



    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Monday, February 14, 2011 10:08 AM

Answers

  • DECLARE	@Sample TABLE
    	(
    		ConsigneeApptID CHAR(2) NOT NULL,
    		PartnerResponse XML NOT NULL
    	)
    
    INSERT	@Sample
    SELECT	'fg',
    	'<mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"></mailxml:PartnerApptQueryResponse>'
    
    -- Before
    SELECT	*
    FROM	@Sample
    
    -- Do the update
    UPDATE	@Sample
    SET	PartnerResponse = REPLACE(CAST(PartnerResponse AS NVARCHAR(MAX)), N'xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"', N'xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"')
    WHERE	ConsigneeApptID = 'fg'
    
    -- After
    SELECT	*
    FROM	@Sample
    
    
    • Marked as answer by Kamran Shahid Monday, February 14, 2011 12:59 PM
    Monday, February 14, 2011 11:57 AM

All replies

  • http://www.developer.com/db/article.php/10920_3755106_2/Working-with-the-XML-Data-Type-of-SQL-Server.htm

     

    Check this link this could help you out......

     

    Please Mark it as Answered once you have got the solution of your question....... !!!!!!!!

    • Proposed as answer by Civic1986 Monday, February 14, 2011 10:16 AM
    Monday, February 14, 2011 10:15 AM
  • Thanks but it could be better if you can help me in exact query

    I were trying

    UPDATE [PartnerAppointmentQueryResponse]  
    Set PartnerApptQueryResponseType.modify
    ('replace value of
    (/PartnerApptQueryResponse[@xmlns])[1] with "http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"
    '
    )
    Where ConsigneeApptID = 'fg'

    but it is not working and i am getting error

    XQuery [PartnerAppointmentQueryResponse.PartnerApptQueryResponseType.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(PartnerApptQueryResponse,xdt:untyped) ?'

    Monday, February 14, 2011 10:25 AM
  • yes

    I wanted to change it to

    <mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml">
    </mailxml:PartnerApptQueryResponse>


    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Monday, February 14, 2011 11:43 AM
  • Thanks Peso

    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Monday, February 14, 2011 1:00 PM