Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1 Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 12:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

.
Post #757167
Posted Wednesday, July 22, 2009 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 4:01 AM
Points: 4, 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
Post #757337
Posted Wednesday, July 22, 2009 7:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Hi Yair,
All these are TSQL examples and hence you can run them in SQL Server Management Studio.

regards
Jacob


.
Post #757409
Posted Wednesday, July 22, 2009 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 20, 2010 6:44 AM
Points: 28, Visits: 26
Nice article.

Would it also be possible to look at the same processes where the XML data is arranged like...
<Employees>
<Employee>
<name>Jacob</name>
<city>NY</city>
<Team>SQL Server</team>
</Employee>
<Employee>
<name>Steve</name>
<city/>
<Team>SQL Server</team>
</Employee>
<Employee>
<name>Bob</name>
<city>CA</city>
<Team/>
</Employee>
</Employees>

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

Thanks
Andy
Post #757516
Posted Wednesday, July 22, 2009 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 11:29 AM
Points: 1, 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
Post #757547
Posted Wednesday, July 22, 2009 10:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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 = '
<Employees>
<Employee name="Jacob" city="NY" Team="SQL Server"/>
<Employee name="Steve" city="FL" Team="SQL Server"/>
<Employee name="Bob" city = "CA" Team="ASP.NET"/>
</Employees>'

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

SET @x.modify('
delete (Employees/Employee[@Team=sql:variable("@Team")]/@city)
')
SELECT @x
/*
<Employees>
<Employee name="Jacob" Team="SQL Server" />
<Employee name="Steve" Team="SQL Server" />
<Employee name="Bob" city="CA" Team="ASP.NET" />
</Employees>
*/



.
Post #757620
Posted Wednesday, July 22, 2009 10:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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...
<Employees>
<Employee>
<name>Jacob</name>
<city>NY</city>
<Team>SQL Server</team>
</Employee>
<Employee>
<name>Steve</name>
<city/>
<Team>SQL Server</team>
</Employee>
<Employee>
<name>Bob</name>
<city>CA</city>
<Team/>
</Employee>
</Employees>

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 = '
<employees>
<employee>
<name>Jacob</name>
<city>NY</city>
<team>SQL Server</team>
</employee>
<employee>
<name>Steve</name>
<city>NY</city>
<team>ASP.NET</team>
</employee>
</employees>'

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

SET @x.modify('
delete (employees/employee[team=sql:variable("@Team")]/city)
')
SELECT @x
/*
<employees>
<employee>
<name>Jacob</name>
<team>SQL Server</team>
</employee>
<employee>
<name>Steve</name>
<city>NY</city>
<team>ASP.NET</team>
</employee>
</employees>
*/



.
Post #757628
Posted Wednesday, July 22, 2009 10:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 20, 2010 6:44 AM
Points: 28, Visits: 26
Your a star !!!
Just realy getting into this XML thing with SQL server, so examples like this are perfect.
Andy
Post #757678
Posted Wednesday, July 22, 2009 10:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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


.
Post #757682
Posted Thursday, July 23, 2009 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 4:01 AM
Points: 4, 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
Post #758004
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse