SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop 24 - Modifying XML Documents Using XQuery Part 1


XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

Author
Message
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2523
Comments posted to this topic are about the item XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

.
ynevet
ynevet
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 11
Hi Jacob,

this is really a good article, very clear and friendly one.

Please tell me, where should I write this code to get the required results from XQuery?

In XML file? in some editor? processor?

Just for example, in order to run C# code I should write it in .cs file extenstion and build it with .NET compiler.... or JavaScript in .js file extention and run it in some browser... etc.

Thank you,

Yair
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2523
Hi Yair,
All these are TSQL examples and hence you can run them in SQL Server Management Studio.

regards
Jacob

.
andrew.sims
andrew.sims
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 26
Nice article.

Would it also be possible to look at the same processes where the XML data is arranged like...


Jacob
NY
SQL Server


Steve

SQL Server


Bob
CA





Which include blank elements.
Have already been caught out with data like this needing insert/modify depending if a value exists

Thanks
Andy
mg-885861
mg-885861
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
In example 10 where you delete the “city” attribute where the “team” is “SQL Server” is there any way to pass in the “SQL Server” as a variable instead of using a constant?

Thanks,
Marlin
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2523
mg (7/22/2009)
In example 10 where you delete the “city” attribute where the “team” is “SQL Server” is there any way to pass in the “SQL Server” as a variable instead of using a constant?

Thanks,
Marlin


Marlin,
You can use a variable as given in the following example

DECLARE @x XML
SELECT @x = '




'

DECLARE @Team VARCHAR(20)
SELECT @Team = 'SQL Server'

SET @x.modify('
delete (Employees/Employee[@Team=sql:variable("@Team")]/@city)
')
SELECT @x
/*





*/



.
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2523
andrew.sims (7/22/2009)
Nice article.

Would it also be possible to look at the same processes where the XML data is arranged like...


Jacob
NY
SQL Server


Steve

SQL Server


Bob
CA





Which include blank elements.
Have already been caught out with data like this needing insert/modify depending if a value exists

Thanks
Andy

Hi Andy,
It is possible by modifying the Xquery expression slightly, as given in the following example


DECLARE @x XML
SELECT @x = '


Jacob
NY
SQL Server


Steve
NY
ASP.NET

'

DECLARE @Team VARCHAR(20)
SELECT @Team = 'SQL Server'

SET @x.modify('
delete (employees/employee[team=sql:variable("@Team")]/city)
')
SELECT @x
/*


Jacob
SQL Server


Steve
NY
ASP.NET


*/



.
andrew.sims
andrew.sims
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 26
Your a star !!!
Just realy getting into this XML thing with SQL server, so examples like this are perfect.
Andy
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2523
Glad to know it helped.
I have written a series of XQuery tutorials at my blog site, in case you need further help.

regards
Jacob

.
ynevet
ynevet
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 11
Hi Jacob,

It did work for me using the SQL query as you advised.

Look, according to your example, we are working against an XML variable, what about working against physical XML file?
It's possible?

Yair
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search