SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / XML Workshop 24 - Modifying XML Documents Using XQuery Part 1 / Latest Posts

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

tymberwyld — Fri, 17 Aug 2012 09:33:03 GMT

I was working on a more "set-based" approach to my above comment. However, the FLWOR statements in SQL don't seem to be working correctly.For example, this works:[code="sql"]SELECT TOP 1 ID, Data.query(' for $n in //address/* where (local-name($n) = ("Line1", "Line2")) return $n ')FROM Addresses[/code]However, the "not equal" does NOT work. Instead, IT RETURNS ALL NODES!?[code="sql"]SELECT TOP 1 ID, Data.query(' for $n in //address/* where (local-name($n) != ("Line1", "Line2")) return $n ')FROM Addresses[/code]Basically, can anyone tell me how to make the "where" clause so that it DOES NOT return nodes with a name of "Line1" and "Line2"?

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

tymberwyld — Fri, 17 Aug 2012 08:38:31 GMT

Good article. I've noticed that the "modify" function can only contain one operation per "SET". However, I want to delete multiple nodes in a piece of XML and not have to code several "SET @x.modify(...)" statements. SQL Server will not let you do "Set-based" operations multiple times on an XML column. So, maybe you can update your article with a way to delete multiple pieces of XML within the document at one time.[b]Use-case:[/b]A table stores "Address" data in an Xml column called "Data". The xml is untyped but generally follows the following "schema":[code="xml"]<Data type="address"> <Line1 /> <Line2 /> <Line3 /> <City /> <State /> <PostalCode /></Data>[/code]However, the programmers have the authority to add any other Nodes they'd like in order to store other things about an address (ex. maybe the "Lat" and "Long" or the CASS Result [USPS Coding Accuracy Support System]). Any stored procedures or queries which need to update this XML data should NEVER remove any of the other nodes that they are not responsible for. In other words, do not simply overwrite the XML data, but rather just manipulate the nodes that are specific to the process at hand.[b]Solution:[/b]The solution to this (because one developer from another will never know ALL the nodes that will exist in the data at any time) is to simply "delete" all the nodes that that developer knows pertains to his process, and then "insert" them back in. This way, if the nodes didn't exist to begin with, then there will be no harm. This will be much cleaner than using "replace value of" functions which rely on the nodes existing up-front (which may not be the case as time goes on and more is added to the XML).[code="sql"]-- Get initial data from recordDECLARE @Data xmlSET @Data = '<Data type="address"> <Line1>123 Seseame St</Line1> <Line2 /> <Line3 /> <City>New York</City> <Region>NY</Region> <PostalCode>12345</PostalCode> <PostalExtension>6789</PostalExtension> <OtherData>My other data</OtherData> <Lat>42</Lat> <Long>42</Long></Data>'SELECT @Data-- Remove known nodes SET @Data.modify(' delete (//*[local-name()=("Line1", "Line2", "Line3", "City", "Region", "PostalCode", "PostalExtension")]) ') -- Insert known nodes with updated dataDECLARE @Line1 nvarchar(50) = '999 Somewhere Ave', @Line2 nvarchar(50) = NULL, @Line3 nvarchar(50) = NULL, @City nvarchar(50) = 'Walawala', @Region nvarchar(50) = 'WA', @PostalCode nvarchar(50) = '99999'DECLARE @Nodes xmlSET @Nodes = ISNULL('<Line1>' + @Line1 + '</Line1>', '') + ISNULL('<Line2>' + @Line2 + '</Line2>', '') + ISNULL('<Line3>' + @Line3 + '</Line3>', '') + ISNULL('<City>' + @City + '</City>', '') + ISNULL('<Region>' + @Region + '</Region>', '') + ISNULL('<PostalCode>' + @PostalCode + '</PostalCode>', '') SET @Data.modify(' insert sql:variable("@Nodes") as first into (/Data)[1] ')SELECT @Data[/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

mypoints01 — Thu, 04 Mar 2010 05:34:10 GMT

It seems to me that I found the solution:DECLARE @XML XMLDECLARE @ElementName VARCHAR(20)SET @ElementName = 'city'SELECT @XML = '<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>'--SET @XML.modify('delete (/employees/employee/*[local-name() = "city"])')SET @XML.modify('delete (/employees/employee/*[local-name() = sql:variable("@ElementName")])')SELECT @XML

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

mypoints01 — Thu, 04 Mar 2010 05:16:37 GMT

DECLARE @XML XMLDECLARE @ElementName VARCHAR(20)SET @ElementName = 'city'SELECT @XML = '<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>'--SET @XML.modify('delete (/employees/employee/city)')--SELECT @XMLBut I need to delete all elements with the name equal @ElementName = 'city' and the name of the element should be variable.Is it possible?Thanks, M

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

Sinh Dinh — Thu, 18 Feb 2010 13:18:54 GMT

Sorry for the late reply. That worked for me. Thanks.

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 21 Oct 2009 20:11:14 GMT

Is it what you are looking for?[code]SELECT ( SELECT XML1, XML2 FROM table1 FOR XML PATH(''), TYPE).query('/*/Response')/*<Response> <Value>Idaho</Value> <Result>ID</Result> <Return>50</Return></Response><Response> <Value>Texas</Value> <Result>TX</Result> <Return>50</Return></Response>*/[/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

Sinh Dinh — Wed, 21 Oct 2009 14:34:34 GMT

I have multiple XML fields with the same schema. Is it possible to "concatenate" them in a select statement to generate a single XML field? Table Schema:[code="sql"]CREATE TABLE Table1 ( ID INT ,XML1 XML ,XML2 XML )[/code]Example:XML Field 1:[code="xml"] <Response> <Value>Idaho</Value> <Result>ID</Result> <Return>50</Return> </Response>[/code]XML Field 2:[code="xml"] <Response> <Value>Texas</Value> <Result>TX</Result> <Return>50</Return> </Response>[/code]Resulting XML Field:[code="xml"] <Response> <Value>Texas</Value> <Result>TX</Result> <Return>50</Return> </Response> <Response> <Value>Texas</Value> <Result>TX</Result> <Return>50</Return> </Response>[/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Thu, 23 Jul 2009 07:02:30 GMT

Well, I would suggest using some other tools such as SSIS if you want to modify the file.

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

ynevet — Thu, 23 Jul 2009 06:55:46 GMT

And it's possible to modify the file it's self using XQuery, that is, updating, deleting, inserting....

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Thu, 23 Jul 2009 06:39:57 GMT

You can load the content of an XML file to an XML variable using OPENROWSET(BULK..). Once the content of the XML file is loaded into the XML variable, you can query it.

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

ynevet — Thu, 23 Jul 2009 04:19:37 GMT

Hi, if your answer is intended for me then I think I didn't clear my self enough.I'm asking if it possible to access XML file from XQuery instead of querying an XML variable.Yair

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Thu, 23 Jul 2009 02:05:17 GMT

Yes, it can work on an XML column too. Here is an example[code]DECLARE @t TABLE (id int, data XML)INSERT INTO @t(id, data) SELECT 1, ' 'INSERT INTO @t(id, data) SELECT 2, ' 'UPDATE @tSET data.modify(' delete (Employees/Employee[@Team="SQL Server"]/@city) ')SELECT * FROM @t [/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

ynevet — Thu, 23 Jul 2009 01:23:15 GMT

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

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 22 Jul 2009 10:55:16 GMT

Glad to know it helped.I have written a series of XQuery tutorials at my blog site, in case you need further help.regardsJacob

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

andrew.sims — Wed, 22 Jul 2009 10:51:42 GMT

Your a star !!!Just realy getting into this XML thing with SQL server, so examples like this are perfect.Andy

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 22 Jul 2009 10:10:09 GMT

[quote][b]andrew.sims (7/22/2009)[/b][hr]Nice article.Would it also be possible to look at the same processes where the XML data is arranged like...[code="xml"] Jacob NY SQL Server Steve SQL Server Bob CA [/code]Which include blank elements.Have already been caught out with data like this needing insert/modify depending if a value existsThanksAndy[/quote]Hi Andy,It is possible by modifying the Xquery expression slightly, as given in the following example[code]DECLARE @x XMLSELECT @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 */[/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 22 Jul 2009 10:05:00 GMT

[quote][b]mg (7/22/2009)[/b][hr]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[/quote]Marlin,You can use a variable as given in the following example[code]DECLARE @x XMLSELECT @x = ' 'DECLARE @Team VARCHAR(20)SELECT @Team = 'SQL Server'SET @x.modify(' delete (Employees/Employee[@Team=sql:variable("@Team")]/@city) ')SELECT @x /* */[/code]

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

mg-885861 — Wed, 22 Jul 2009 09:25:27 GMT

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

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

andrew.sims — Wed, 22 Jul 2009 09:02:37 GMT

Nice article.Would it also be possible to look at the same processes where the XML data is arranged like...[code="xml"] Jacob NY SQL Server Steve SQL Server Bob CA [/code]Which include blank elements.Have already been caught out with data like this needing insert/modify depending if a value existsThanksAndy

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 22 Jul 2009 07:17:36 GMT

Hi Yair,All these are TSQL examples and hence you can run them in SQL Server Management Studio.regardsJacob

RE: XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

ynevet — Wed, 22 Jul 2009 06:08:50 GMT

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

XML Workshop 24 - Modifying XML Documents Using XQuery Part 1

jacob sebastian — Wed, 22 Jul 2009 00:38:36 GMT

Comments posted to this topic are about the item [B]XML Workshop 24 - Modifying XML Documents Using XQuery Part 1[/B]