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 Labs - A Collection of XQuery Sample Scripts

Jun 26 2008 3:41PM by Jacob Sebastian   

I have always experienced and am convinced that, while learning a programming technique, it is more helpful to see an example that matches our specific requirement, rather than some theoretical stuff. Examples are really good for learning the basics and then the theories will help us to go deeper into the specific technique.

SQL Server 2005 introduced XML data type and a few related technologies like XQuery, XSD etc. People having no previous experience with XQuery or XSD might find it little hard to find a starting point. Initially, this might seem little complex and then as you go ahead, things will get smoother.

I am trying to post a series of XQuery sample scripts. Most of these scripts were created to help people who needed help at various online forums. I guess some of you will find a few examples that closely matches with your specific requirement and will give enough hints to solve the problem you have at hand.

If you don't find an example that matches with your requirement, you could leave me a note and I will try to add it. Given below is the list of XQuery sample scripts that I have currently. I will keep this page updated when a new script is added. So probably it is a good idea to bookmark this page, if you want to keep a watch on the new scripts being posted. Alternatively, you could subscribe to the RSS/ATOM feeds available on this page and instantly get noticed about new posts as well as updated on existing posts.

XQuery Sample Scripts

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


Jacob Sebastian
1 · 100% · 32235
3
 
 
0
Refreshed
 
 
0
Incorrect



Submit

9  Comments  

  • Hello Jacob,

    Sorry for the trouble, did you get a chance to look at the issue I am facing, I am still not able to find the values for example in the XML below when th eentire XML is being parsed. I am unable to parse the values such as :- Created, Service fodered,Service srikhand Rep Assigned

    in the below mentioned XML. I have also given to you my Xquery which I am struggling with .

    Please Help. Thanks in advance,

    The XML

    <hailstroms count="8" current="5">   
    <hailstrom completed="2009-04-09" duration="0" name="created" sequence="1">Created</hailstrom> 
    <hailstrom completed="2009-04-09" duration="2" name="fodered" sequence="2">Service fodered</hailstrom>   
    <hailstrom completed="2009-04-09" duration="2" name="Karlerep" sequence="3">Service srikhand Rep Assigned</hailstrom>        
    <hailstrom completed="2009-04-09" duration="8" name="provision" sequence="4">In Provisioning</hailstrom>  
    <hailstrom completed="2009-04-09" duration="2" name="lineconfrec" sequence="5">Line Confirmation RCVD</hailstrom>        
    <hailstrom duration="4" name="custcont" sequence="6">Dhokala Contacted</hailstrom>         
    <hailstrom duration="4" name="fodercomplete" sequence="7">Line BUSTALed</hailstrom>         
    <hailstrom duration="0" name="lineinst" sequence="8">foder Complete</hailstrom>       
    </hailstroms>
    
    commented on Feb 7 2012 9:40AM
    mohandeval
    2033 · 0% · 8
  • Hello Jacob, great blog and examples on XQuery, I really liked it.

    I cant find one example that "How to update the value of an element from a table". for example if I have following xml

    <TABLE>
            <LABEL COLOR="RED"> Account #: </LABEL>
            <VALUE> AcctNum </VALUE>
            <LABEL COLOR="RED"> Emp. Name: </LABEL>
            <VALUE> EmpName </VALUE>
    </TABLE>
    

    And I have a following table named EmployeeAccount

    **AcctNum**   **EmpName**
    1234                 Kash
    

    then how can I write xquery to get following output (using join the xquery with the table)

    <TABLE>
            <LABEL COLOR="RED"> Account #: </LABEL>
            <VALUE> 123 </VALUE>
            <LABEL COLOR="RED"> Emp. Name: </LABEL>
            <VALUE> Kash </VALUE>
    </TABLE>
    

    here values are coming from table.

    any help..

    commented on Apr 3 2012 8:24AM
    kash
    2834 · 0% · 3
  • Mohan,

    is this what you are looking for?

    DECLARE @x XML
    SELECT @x = '
    <hailstroms count="8" current="5">   
    <hailstrom completed="2009-04-09" duration="0" name="created" sequence="1">Created</hailstrom> 
    <hailstrom completed="2009-04-09" duration="2" name="fodered" sequence="2">Service fodered</hailstrom>   
    <hailstrom completed="2009-04-09" duration="2" name="Karlerep" sequence="3">Service srikhand Rep Assigned</hailstrom>        
    <hailstrom completed="2009-04-09" duration="8" name="provision" sequence="4">In Provisioning</hailstrom>  
    <hailstrom completed="2009-04-09" duration="2" name="lineconfrec" sequence="5">Line Confirmation RCVD</hailstrom>        
    <hailstrom duration="4" name="custcont" sequence="6">Dhokala Contacted</hailstrom>         
    <hailstrom duration="4" name="fodercomplete" sequence="7">Line BUSTALed</hailstrom>         
    <hailstrom duration="0" name="lineinst" sequence="8">foder Complete</hailstrom>       
    </hailstroms>'
    
    SELECT
    	x.value('hailstrom[@name="created"][1]','VARCHAR(20)') AS CreatedValue,
    	x.value('hailstrom[@name="created"][1]','VARCHAR(20)') AS foderedValue
    FROM @x.nodes ('/hailstroms') a(x)
    
    /*
    CreatedValue         foderedValue
    -------------------- --------------------
    Created              Created
    */
    
    commented on Apr 4 2012 8:07AM
    Jacob Sebastian
    1 · 100% · 32235
  • Kash,

    I think it will be much easier if you attempt to rebuild the XML using FOR XML with specific values embedded within elements/attributes. That will be much easier than attempting to update each element.

    commented on Apr 4 2012 8:08AM
    Jacob Sebastian
    1 · 100% · 32235
  • Jacob, May be I failed to explain my requirement,

    Actually I want to have xml as template and when required I will get that xml with updated values from table.

    I solved this problem using following

    DECLARE @tblXML TABLE (ID INT, X XML)
    
    INSERT INTO @tblXML (ID, X)
    SELECT  	1, '<table id="ACCT_NUM">
        					<label>Acct #:</label>
        					<value>val</value>
        				</table>
        				<table id="PROD">
        					<label>Product:</label>
        					<value>val</value>
        				</table>'
    
    
    --ACCT_NUM
    UPDATE  	tblXML
    SET 		X.modify('replace value of (//table[@id="ACCT_NUM"]/value/text())[1] with sql:column("ACCT_NUM")')
    FROM    	@tblXML tblXML
    INNER JOIN  EmployeeAccount eld ON eld.ID = tblXML.ID
    
    --PROD
    UPDATE  	tblXML
    SET 		X.modify('replace value of (//table[@id="PROD"]/value/text())[1] with sql:column("PROD")')
    FROM    	@tblXML tblXML
    INNER JOIN  EmployeeAccount ea ON ea.ID = tblXML.ID
    
    SELECT X FROM @tblXML
    

    I did little bit change in xml, introduced the id attribute to accessed the node from that specific attributed and then replaced the value from EmployeeAccount table.

    now my problem is that if I want to replace multiple values from their respective column than I need to write multiple update queries (as I did in above example), Can I write above two update queries in single query?

    commented on Apr 5 2012 3:02AM
    kash
    2834 · 0% · 3
  • It appears that only one element can be updated at a time. So you will need a loop to update each value one at a time. That is the reason why I suggested you rebuild the XML (if the structure is known in advance) using a FOR XML query.

    commented on Apr 5 2012 4:52AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hello. I've found your page of XQuery labs very helpful, but I don't see any examples of writing a table to a new XML file. Or of using a FOR XML query.

    commented on Jun 7 2012 8:55AM
    xerogold
    3057 · 0% · 2
  • Hi Jacob.

    I'm astonished with your articles and labs regarding xml. I'm an experienced dba managing more than 130 SQL Server instances in a financial firm at Spain.

    Several months ago I decided improve my XML skills in order to become a more powerfull admin using sql server features as Extended Events and Query Plan analysis.

    Your articles have been the fuel I needed for take this step. Easy and clear content.

    Thank you for your help within this site.

    commented on Dec 28 2012 3:43AM
    jvinualzar
    3057 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"XQuery Labs - A Collection of XQuery Sample Scripts" rated 5 out of 5 by 3 readers
XQuery Labs - A Collection of XQuery Sample Scripts , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]