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'=AddAttachmen
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.
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'=AddAttachmen
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.
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.
ASKER
only part of the data from the xmldata column, only the information between the <contents> </contents> xml tags
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><MaleFema
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><MaleFema
<Content></content></xml>
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><MaleFema
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><MaleFema
<Content></content></xml>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfecto! that's great scot works great - thanks very much ill be sure to try and work out how it works!
ASKER
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.
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>M
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:
For example, assuming that your XmlData actually looks like this:
<?xml version="1.0" encoding="utf-8"?><title>M
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))
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