views:

92

answers:

1

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>
          &lt;p&gt;Pedro de Valdivia 291&lt;/p&gt;&lt;p&gt;Providencia&lt;/p&gt;&lt;p&gt;Santiago&lt;/p&gt;
        </ADDRESS>
        <LATITUDE>-33.426127</LATITUDE>
        <LONGITUDE>-70.611469</LONGITUDE>
        <BUSINESSUNITS>Copper</BUSINESSUNITS>        
        <EMPLOYEES />
        <NUMBEROFBUSINESS>1</NUMBEROFBUSINESS>
      </COUNTRY>
      <COUNTRY TITLE="Brazil">
        <HEADOFFICE>Brazil</HEADOFFICE>
        <ADDRESS>
          &lt;p&gt;Pedro de Valdivia 291&lt;/p&gt;&lt;p&gt;Providencia&lt;/p&gt;&lt;p&gt;Santiago&lt;/p&gt;
        </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.

+1  A: 

Edit 2: It looks like SLQ-Server is not a fully complaint XQuery processor...

So, a more "static" XQuery (wich is fine is the schema is well known):

declare variable $continent external;
declare variable $country external;
declare variable $xml as item() external;
element DATA {
   /DATA/@*,
   /DATA/*[not(self::LOCATIONS)],
   element LOCATIONS {
      /DATA/LOCATIONS/@*,
      for $cont in /DATA/LOCATIONS/CONTINENT
      return element CONTINENT {
                $cont/@*,
                for $count in $cont/COUNTRY
                return if ($cont/@TITLE=$continent and
                           $count/@TITLE=$country)
                       then $xml
                       else $count
             }
   }
}

With this parameters (With SQL-Server you could also use sql:variable() extension function):

continent="'South America'"
country="'Brazil'"
xml="<COUNTRY TITLE='Brazil'><UPDATE/></COUNTRY>"

Output:

<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>
          &lt;p&gt;Pedro de Valdivia 291&lt;/p&gt;&lt;p&gt;Providencia&lt;/p&gt;&lt;p&gt;Santiago&lt;/p&gt;
                </ADDRESS>
                <LATITUDE>-33.426127</LATITUDE>
                <LONGITUDE>-70.611469</LONGITUDE>
                <BUSINESSUNITS>Copper</BUSINESSUNITS>
                <EMPLOYEES/>
                <NUMBEROFBUSINESS>1</NUMBEROFBUSINESS>
            </COUNTRY>
            <COUNTRY TITLE="Brazil">
                <UPDATE/>
            </COUNTRY>
        </CONTINENT>
    </LOCATIONS>
</DATA>

EDIT 3: I think that the correct syntax for your query in comments is:

declare @continent varchar(100) 
declare @country varchar(100) 
declare @xml xml 
declare @count int 
declare @doc xml 
set @continent='South America' 
set @country='Brazil' 
set @xml='<COUNTRY TITLE="Brazil"><UPDATE/></COUNTRY>' 
set @count = 1 
select @doc = xmldesc from varunxmlanglo where idlanguage =1 and xmltype ='D' 
select @doc.query('
element DATA {
   /DATA/@*,
   /DATA/*[not(self::LOCATIONS)],
   element LOCATIONS {
      /DATA/LOCATIONS/@*,
      for $continent in /DATA/LOCATIONS/CONTINENT
      return element CONTINENT {
                $continent/@*,
                for $country in $continent/COUNTRY
                return if ($continent/@TITLE=sql:variable("@continent") and                                                       $country/@TITLE=sql:variable("@country"))
                       then sql:variable("@xml")
                       else $country
             }
   }
}
')

Note: In XQuery variable reference have $ prefixed.

Alejandro
@Alejandro: Good answer, +1.
Dimitre Novatchev
@Dimitre: Thanks! I began to like answering questions about XQuery.
Alejandro
Hi Alejandro... thanks for replying. I am bit new to xquery and wat i did was pasted ur code to the sql query window and found that it does not work. Can you please let me know where exactly i need to place this code?
Varun
@Varun: Sorry, but according to this http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx#sql2k5_xqueryintro_topic11 there is few of XQuery specs supported be SLQ Server 2005... User defined functions, especially.
Alejandro
Thanks Alejandro.. Wil give it a try and get back to you if stuck.
Varun
Hi Alejandro, it did not work?I dont know how this works for you. LEt me tell you that I am using .NET 2008 and sql server 2005. Can you please guide stepwise how to make it work?
Varun
Yes Alejandro, we can delete this way and i came to know this, but what about inserting the updated node at the same location. eg: user modified the data for country 'brazil', so i go by above query and deltete the data but i have to update the same node with the modified data right. How we can do insert at the same location from where we have deleted?
Varun
@Varun: Check my new XQuery answer.
Alejandro
Hi Alejandro as I told it does not work , i am using sql server 2005 and .NET 2008, if i paste your above modified xquery in the query window of sql server 2005, i get error and the error is as below ::: Msg 102, Level 15, State 1, Line 2Incorrect syntax near '$continent'.Msg 113, Level 15, State 1, Line 29Missing end comment mark '*/'.
Varun
@Varun: Did you try `SELECT @x.query('...')` and replacing those external variables with `sql:variable()` extension function?
Alejandro
Here is my code but i m getting syntax error::begin tran t2declare @continent varchar(100)declare @country varchar(100)declare @xml xmlset @continent='South America'set @country='Brazil'set @xml='<COUNTRY TITLE="Brazil"><UPDATE/></COUNTRY>'declare @count int set @count = 1declare @doc xmlselect @doc = xmldesc from varunxmlanglo where idlanguage =1 and xmltype ='D'
Varun
select @doc.query(element DATA { /DATA/@*, /DATA/*[not(self::LOCATIONS)], element LOCATIONS { /DATA/LOCATIONS/@*, for @continent in /DATA/LOCATIONS/CONTINENT return element CONTINENT { @continent/@*, for @count in @continent/COUNTRY return if (@continent/@TITLE=sql:variable("@continent") and @count/@TITLE=sql:variable("@country") then sql:variable("@xml") else @count } }}
Varun
I am not clear about your query especially the syntaxes, because now i have started basis Xquery and have learned to fetch child nodes, attributes values etc.. simple stuff. And the code which you have done seems bit complex to me. So i request you to simplify or comment in the code so that i come to know which line does what
Varun
Also I would like to have your personal email id so that we can talk and once we get the solution, i can post on this blog for others to get help.
Varun
Hi Alejandro thanks for support, I tried your code but i get error ::XQuery: SQL type 'xml' is not supported in XQuery.
Varun
@Varun: Yes, I see it. From http://msdn.microsoft.com/en-us/library/ms188254.aspx: *You can only refer to an xml instance in the context of the source expression of an XML-DML insert statement; otherwise you cannot refer to values that are of type xml or a common language runtime (CLR) user-defined type.* I'll look up how to pass XML instance as parameter.
Alejandro
Thanks Alejandro
Varun