Link to home
Avatar of Spikeuk30
Spikeuk30

asked on

SQL : remove table data

SQL :

I have a large SQL table (called uploads) i wish to trim a lot of data out of to reduce the overall filesize which is balooning.


I am no SQL expert so any help would be greatly appreciated.

Uploaded files are saved directly into a database in a column as xml data.   The column is called 'XMLData'

I want to seach the table and remove uploaded file data from the 'xmldata' column that is older than 1 month. - this task will be run on a regular basis, am i best off using a query or a stored procedure?  I dont know whats best?

Basically this is what i want to do....

Where column 'messagetype'=AddAttachment' or 'App'

and 'datestamp' column is more than 1 month old..

remove the content data between the tags <contents> </contents>  in the  'XMLdata' column.

Thanks for any help and pointers in advance.
Avatar of Sharath S
Sharath S
Flag of United States of America image

Do you want to delete the entire record if it is older than 1 month or want to remove the data from XML column when the record is 1 month old. In the later case, provide some sample data from your table.
Avatar of Spikeuk30
Spikeuk30

ASKER

only part of the data from the xmldata column, only the information between the  <contents>  </contents> xml tags
Sample data:

3 columns:

MessageType  | DateStamp | XMLData



MessageType
I am only interested in rows with 'Application' or 'AddAttachment' as MessageType.

DateStamp:
self explanatory  e.g.   2012-02-20 10:30:05.673

XML Data:
<?xml version="1.0"  encoding "ut-8"?> <title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality> <Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</content></xml>


I only want to amend the XML removing the data between the Content tags so I end up with this:

XML Data:
<?xml version="1.0"  encoding "ut-8"?> <title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality>
<Content></content></xml>
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Perfecto!  that's great scot works great - thanks very much ill be sure to try and work out how it works!
Perfect works great!
Uploaded files are saved directly into a database in a column as xml data.   The column is called 'XMLData'
Typically the way you do this is using the Xml Data Type Methods, unfortunately the Xml you posted is not well-formed and therefore not a valid Xml document, so your only choice is to go with Scott's solution.
If it was well-formed the actual solution is quite trivial.  

For example, assuming that your XmlData actually looks like this:
<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>

Notice that I have corrected the encoding, added the necessary = sign as well as corrected the last tag to "Content"  (Xml is case-sesitive)

Your solution is as simple as this:
UPDATE  uploads
SET     XMLData.modify('delete Content')        -- if the Xml is any different then what you posted you would modify this XPath appropriately
WHERE   MessageType IN ('AddAttachment', 'Application')
        AND DateStamp < DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Open in new window

This is how I tested it:
DECLARE @uploads TABLE (MessageType varchar(20), DateStamp datetime, XMLData xml)
INSERT @uploads  (MessageType, DateStamp, XMLData) 
VALUES ('Application', '2012-02-20 10:30:05.673', '<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>'),
	   ('AddAttachment', '2012-02-20 10:30:05.673', '<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>'),
	   ('Application', '2014-04-20 10:30:05.673', '<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>'),
	   ('AddAttachment', '2014-04-20 10:30:05.673', '<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>'),
	   ('SomethingElse', '2012-02-20 10:30:05.673', '<?xml version="1.0" encoding="utf-8"?><title>Mr</title><MaleFemale>M</MaleFemale><Nationality>British</Nationality><Content>asjkldhdkajhdgwgejsgdjhgdjasdvansdjahdgajshd</Content>')

SELECT *
FROM @uploads

UPDATE  @uploads
SET     XMLData.modify('delete Content')
WHERE   MessageType IN ('AddAttachment', 'Application')
        AND DateStamp < DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

SELECT *
FROM @uploads

Open in new window