Hi All,
I am storing an xml file in the xml datatype in table of SQL Server. Now I want to fetch some fragments (using xquery) and then update the fragments with the modified fragments (using xquery). I need some suggestions.
I have the code to delete a node and it is as below but while deleting I need to insert the modified node at the same place. How can this be done?
--SET @doc.modify('delete (/DATA/SDACTS)')
This works if I want to delete a node but what about inserting the modified node at same location
Next I want to delete the nodes whose values will be passed as querystring so I have to build up the string
like below
DECLARE @x XML
SET @x = '
<DATA>
<SDACTS>
<SDACT TYPE="Economy" COLOUR="0xff0000" />
<SDACT TYPE="Environment" COLOUR="0x00ff00" />
<SDACT TYPE="People" COLOUR="0x0000ff" />
<SDACT TYPE="Society" COLOUR="0xff00ff" />
</SDACTS>
<LOCATIONS>
<CONTINENT TITLE="South America">
<COUNTRY TITLE="Chile">
<HEADOFFICE>Santiago</HEADOFFICE>
<ADDRESS>
<p>Pedro de Valdivia 291</p><p>Providencia</p><p>Santiago</p>
</ADDRESS>
<LATITUDE>-33.426127</LATITUDE>
<LONGITUDE>-70.611469</LONGITUDE>
<BUSINESSUNITS>Copper</BUSINESSUNITS>
<EMPLOYEES />
<NUMBEROFBUSINESS>1</NUMBEROFBUSINESS>
</COUNTRY>
<COUNTRY TITLE="Brazil">
<HEADOFFICE>Brazil</HEADOFFICE>
<ADDRESS>
<p>Pedro de Valdivia 291</p><p>Providencia</p><p>Santiago</p>
</ADDRESS>
<LATITUDE>-38.426127</LATITUDE>
<LONGITUDE>-60.611469</LONGITUDE>
<BUSINESSUNITS>Zinc</BUSINESSUNITS>
<EMPLOYEES />
<NUMBEROFBUSINESS>2</NUMBEROFBUSINESS>
</COUNTRY>
</CONTINENT>
</LOCATIONS>
</DATA>
I have to delete the country brazil which lies in the continent South America so I have to keep these as parameters which has to be dynamic as other country and continent can come.
declare @country varchar(50)
declare @continent varchar(50)
set @country = 'Brazil'
set @continent = 'South America'
declare @final varchar(100)
set @final = '//CONTINENT[@TITLE="' + @continent + '"]/COUNTRY[@TITLE="' + @country + '"]'
select @final
--SELECT @doc.query('sql:variable("@final")') 'XmlDesc' This works for select statement but not for delete
SET @doc.modify('delete (sql:variable("@final"))') This does not work and gives error.
SELECT @doc
My requirement:
Basically I am making a xml editing tool in .NET and jQuery tree hangs when loading huge xml files so I am storing the huge xml file in table and calling the segments (child nodes) and then loading those segments in jquery tree and user modifies those node. I take the modified segments to the database and then want to update with the modified one.