|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Add perent elementI solve that problem with: DECLARE @a xml SET @a = N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>' SELECT @a SET @a.modify(' insert (<AddElement></AddElement>) after (/EVENT_INSTANCE/a)[1]') select @a SET @a.modify(' insert /EVENT_INSTANCE/b1 as first into (/EVENT_INSTANCE/AddElement)[1]') select @a SET @a.modify(' delete /EVENT_INSTANCE/b1') SELECT @a Is there any simplier solution. Thanks Dear Ana
There are two ways at the moment to insert a new intermediate layer. The one you show below, the other (which may be worse), is to decompose the tree and use the FOR XML PATH mode to recompose the tree with the new level in between. The three updates below - unlike the FOR XML approach - will only update the subtrees involved in the updates and not the whole XML document. We are looking into extending the update language to make this a bit better, but in general, inserting new levels into an XML tree will always be a somewhat costly operation since your three steps would have to be executed in any case (due to the node-id implementation that encodes the position in the tree). Best regards Michael Show quoteHide quote "Ana Mihalj" <AnaMih***@discussions.microsoft.com> wrote in message news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@microsoft.com... >I want to add perent element to existing element in xml variable (SQL >2005). > I solve that problem with: > DECLARE @a xml > SET @a = > N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>' > SELECT @a > > SET @a.modify(' > insert (<AddElement></AddElement>) > after (/EVENT_INSTANCE/a)[1]') > select @a > > > SET @a.modify(' > insert /EVENT_INSTANCE/b1 > as first > into (/EVENT_INSTANCE/AddElement)[1]') > select @a > > SET @a.modify(' > delete /EVENT_INSTANCE/b1') > SELECT @a > > Is there any simplier solution. > > Thanks Thanks Michael.
Show quoteHide quote "Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message news:OmzYQl06FHA.2716@TK2MSFTNGP11.phx.gbl... > Dear Ana > > There are two ways at the moment to insert a new intermediate layer. The > one you show below, the other (which may be worse), is to decompose the > tree and use the FOR XML PATH mode to recompose the tree with the new > level in between. > The three updates below - unlike the FOR XML approach - will only update > the subtrees involved in the updates and not the whole XML document. > > We are looking into extending the update language to make this a bit > better, but in general, inserting new levels into an XML tree will always > be a somewhat costly operation since your three steps would have to be > executed in any case (due to the node-id implementation that encodes the > position in the tree). > > Best regards > Michael > > "Ana Mihalj" <AnaMih***@discussions.microsoft.com> wrote in message > news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@microsoft.com... >>I want to add perent element to existing element in xml variable (SQL >>2005). >> I solve that problem with: >> DECLARE @a xml >> SET @a = >> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>' >> SELECT @a >> >> SET @a.modify(' >> insert (<AddElement></AddElement>) >> after (/EVENT_INSTANCE/a)[1]') >> select @a >> >> >> SET @a.modify(' >> insert /EVENT_INSTANCE/b1 >> as first >> into (/EVENT_INSTANCE/AddElement)[1]') >> select @a >> >> SET @a.modify(' >> delete /EVENT_INSTANCE/b1') >> SELECT @a >> >> Is there any simplier solution. >> >> Thanks > > One way is xquery, optionally combined with update query.
select @a.query(' for $a in /* return ( element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c} ) ') -- Show quoteHide quotePohwan Han. Seoul. Have a nice day. "Ana Mihalj" <amih***@hotmail.com.false> wrote in message news:%23Q2O2326FHA.2524@TK2MSFTNGP10.phx.gbl... > Thanks Michael. > > > > "Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message > news:OmzYQl06FHA.2716@TK2MSFTNGP11.phx.gbl... >> Dear Ana >> >> There are two ways at the moment to insert a new intermediate layer. The >> one you show below, the other (which may be worse), is to decompose the >> tree and use the FOR XML PATH mode to recompose the tree with the new >> level in between. >> The three updates below - unlike the FOR XML approach - will only update >> the subtrees involved in the updates and not the whole XML document. >> >> We are looking into extending the update language to make this a bit >> better, but in general, inserting new levels into an XML tree will always >> be a somewhat costly operation since your three steps would have to be >> executed in any case (due to the node-id implementation that encodes the >> position in the tree). >> >> Best regards >> Michael >> >> "Ana Mihalj" <AnaMih***@discussions.microsoft.com> wrote in message >> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@microsoft.com... >>>I want to add perent element to existing element in xml variable (SQL >>>2005). >>> I solve that problem with: >>> DECLARE @a xml >>> SET @a = >>> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>' >>> SELECT @a >>> >>> SET @a.modify(' >>> insert (<AddElement></AddElement>) >>> after (/EVENT_INSTANCE/a)[1]') >>> select @a >>> >>> >>> SET @a.modify(' >>> insert /EVENT_INSTANCE/b1 >>> as first >>> into (/EVENT_INSTANCE/AddElement)[1]') >>> select @a >>> >>> SET @a.modify(' >>> delete /EVENT_INSTANCE/b1') >>> SELECT @a >>> >>> Is there any simplier solution. >>> >>> Thanks >> >> > > Thanks Han.
Show quoteHide quote "Han" <hp4***@kornet.net.korea> wrote in message N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</cnews:ORUxMN#6FHA.268@TK2MSFTNGP10.phx.gbl... > One way is xquery, optionally combined with update query. > > select @a.query(' > for $a in /* return ( > element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c} > ) > ') > > -- > Pohwan Han. Seoul. Have a nice day. > "Ana Mihalj" <amih***@hotmail.com.false> wrote in message > news:%23Q2O2326FHA.2524@TK2MSFTNGP10.phx.gbl... > > Thanks Michael. > > > > > > > > "Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message > > news:OmzYQl06FHA.2716@TK2MSFTNGP11.phx.gbl... > >> Dear Ana > >> > >> There are two ways at the moment to insert a new intermediate layer. The > >> one you show below, the other (which may be worse), is to decompose the > >> tree and use the FOR XML PATH mode to recompose the tree with the new > >> level in between. > >> The three updates below - unlike the FOR XML approach - will only update > >> the subtrees involved in the updates and not the whole XML document. > >> > >> We are looking into extending the update language to make this a bit > >> better, but in general, inserting new levels into an XML tree will always > >> be a somewhat costly operation since your three steps would have to be > >> executed in any case (due to the node-id implementation that encodes the > >> position in the tree). > >> > >> Best regards > >> Michael > >> > >> "Ana Mihalj" <AnaMih***@discussions.microsoft.com> wrote in message > >> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@microsoft.com... > >>>I want to add perent element to existing element in xml variable (SQL > >>>2005). > >>> I solve that problem with: > >>> DECLARE @a xml > >>> SET @a = > >>> Show quoteHide quote ></EVENT_INSTANCE>' > >>> SELECT @a > >>> > >>> SET @a.modify(' > >>> insert (<AddElement></AddElement>) > >>> after (/EVENT_INSTANCE/a)[1]') > >>> select @a > >>> > >>> > >>> SET @a.modify(' > >>> insert /EVENT_INSTANCE/b1 > >>> as first > >>> into (/EVENT_INSTANCE/AddElement)[1]') > >>> select @a > >>> > >>> SET @a.modify(' > >>> delete /EVENT_INSTANCE/b1') > >>> SELECT @a > >>> > >>> Is there any simplier solution. > >>> > >>> Thanks > >> > >> > > > > >  
Other interesting topics
Getting Empty Tags Returned From The Server
Return Value from SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute xml-dml insert element with an attribute containig sql:variable trouble with annotated xsd mapping schemas sending xml data from stored procedure XML Comparsion grammer Retrieve data from text field containing XML data Query Notification Tree structure data in xml .. xml web service as a datasource ? |
|||||||||||||||||||||||
