Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 54 – Modifying an XML document with a SELECT Query

Mar 31 2010 6:37PM by Jacob Sebastian   

Here is an interesting XQuery requirement that one of my friends sent me. The challenge is to modify an XML document stored in an XML column as part of a SELECT query.

Here is a simplified representation of the problem. Take a look at the source data before we proceed with the query.

id          data
----------- ----------------------------------
1           <data><code>Le code</code></data>

The task is to add a new element to the XML document as part of a SELECT query. The XML document needs to have an additional element to store the “ID” of the current record.  Here is the expected result:

----------- -------------------------------------------
1           <data><id>1</id><code>Le code</code></data>

There are a number of different ways to generate the above output as part of a select query. The following code snippet demonstrates one way of doing this.

DECLARE @t TABLE (
    id INT,
    data XML )

INSERT INTO @t (id, data)
SELECT 1, '
    <data>
        <code>Le code</code>
    </data>'


SELECT id, 
    data.query('
    <data>
        <id>{sql:column("id")}</id>
        {/data/code}
    </data>')
FROM @t

/*
<data>
  <id>1</id>
  <code>Le code</code>
</data>
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Visit the XML Resource Center for more XML articles and tutorials.

Tags: XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32235
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

6  Comments  

  • Is it possible to have this kind of output? /* Le code */ I've a similar kind of requirement in my project. Please help.

    commented on Jun 21 2010 4:29AM
    sanjay
    193 · 1% · 246
  • Yes, it is quite possible. Here is an example:

    DECLARE @t TABLE ( id INT, data XML )

    INSERT INTO @t (id, data) SELECT 1, ' Le code '

    SELECT id, data.query(' {/data/code} ') FROM @t

    /* Le code */

    commented on Jun 21 2010 5:34AM
    Jacob Sebastian
    1 · 100% · 32235
  • Is it possible to do the same when each element have only attributes?

    DECLARE @t TABLE (
        id INT,
        data XML )
    
     INSERT INTO @t (id, data) SELECT 1, '<data  @code="Le code"  </>'
    
     The result would be after select.
    
     <data id="1" code="Le code" />
    
    commented on Oct 29 2013 11:11PM
    Maurice Pelchat
    1349 · 0% · 17
  • It is possible, see this example:

    DECLARE @t TABLE (id INT, data XML )
    INSERT INTO @t (id, data) SELECT 1, '<data  code="Le code" />'
    
    SELECT
    	data.query('
    		<data id="{sql:column("id")}" code="{/data/@code}" />
    	')
    FROM @t 
     /*
      Output:
      <data id="1" code="Le code" />
    */
    
    commented on Nov 10 2013 10:57AM
    Jacob Sebastian
    1 · 100% · 32235
  • Thanks a lot Jacob,

    Your section about XQuery in SQL Server is the one most comprehensive and useful on that subject on the NET.

    I'm working one some useful application of Xquery to SQL problem solving. Eventually I'll share them with you.

    commented on Nov 10 2013 1:52PM
    Maurice Pelchat
    1349 · 0% · 17
  • Hi Maurice, I am glad to hear that you found this to be helpful.

    commented on Nov 17 2013 10:26AM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]