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>

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..

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
*/

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.

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?

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.

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.

Please see if this helps http://beyondrelational.com/modules/2/blogs/28/posts/10343/for-xml-tutorials.aspx

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.