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.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Sharath S

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.
Spikeuk30

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

ASKER
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>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Spikeuk30

ASKER
Perfecto!  that's great scot works great - thanks very much ill be sure to try and work out how it works!
Spikeuk30

ASKER
Perfect works great!
Anthony Perkins

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

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

Anthony Perkins

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