Deleted data from table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deleted data from table

Author

Topic

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 02:01:02

Hi to All,

Please help for this, whenever i delete a data from table that deleted data will store in xml file so now i want when i run query on that deleted table i need all data from table and xml also

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 04:13:11

you need to use OPENROWSET BULK to get xml imported to temporary table
something like


INSERT INTO Tablename(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'your xml file path here',
   SINGLE_BLOB) AS x

then use .nodes() to access data from table and union all data with 
select columns... FROM yourtable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 04:46:45

Hi Visakh,

Thanks for reply
i am using this statement but i am not able to get results,please help me with this.

CREATE TABLE #XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)

SELECT @xmlFileName = '\\cespl-pc9\testing\Register.xml'

--– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

DECLARE @foo XML

SET @foo = (SELECT xml_data from #XmlImportTest)

SELECT
CAST(y.item.query('data(FundName)') AS varchar(30)),
CAST(y.item.query('data(FundValue)') AS char(25))

FROM
@foo.nodes('/*') x(item)
CROSS APPLY
x.item.nodes('./*') AS y(item)

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 04:53:49

you cant use # tables inside dynamic sql as it would be out of scope. you can use permanent table or ##tables instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 05:08:05

Hi,

i run that statement by using ##table but my result is two blank column.Now what i have to do please tell me

Thanku

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:18:16

first try running below and see if its working


INSERT INTO Tablename(XmlCol)
SELECT * FROM OPENROWSET(
   BULK '\\cespl-pc9\testing\Register.xml',
   SINGLE_BLOB) AS x

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 05:32:53

Hi,

First i don't want to store that xml file data into my physical table.I just want to see that data.

i am currently have data in Table A. i process that deletes the data from Table A, that exports the data before deletion and stores the data in an xml file stored on the file system. What i wanting to be able to do is when i run a select statement against Table A, i want the result set to include the data from the xml file as well as the data from Table A.

I run that above statement i got this error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Tablename'.

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:34:11

please put the actual tablename (create a temporary table and use it)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 06:16:18

I using this statement and i getting error

CREATE TABLE ##XmlImportTest1(RegId int,
FirstName varchar(100),LastName varchar(100),Email varchar(100),Passwords bigint,
SalespersonType varchar(100),CreatedDate_dt datetime,UsetType varchar(100),SalesPerson_ID int,LoginCount int
)
GO

INSERT INTO ##XmlImportTest1(RegId,
FirstName,LastName,Email,Passwords,SalespersonType,CreatedDate_dt,UsetType,SalesPerson_ID,LoginCount)

SELECT RegId,FirstName,LastName,Email,Password,SalespersonType,CreatedDate_dt,UsetType,SalesPerson_ID,LoginCount
FROM OPENROWSET(BULK '\\cespl-pc9\testing\Register.xml',
SINGLE_BLOB) AS x

-----Error-------

Msg 207, Level 16, State 1, Line 1
Invalid column name 'RegId'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'FirstName'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LastName'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Email'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Password'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SalespersonType'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'CreatedDate_dt'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'UsetType'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SalesPerson_ID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LoginCount'.

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 06:22:56

you can shred data from xml like this. first put xml data as a who;e in table using below and then apply nodes() function to shred out the data


CREATE TABLE ##XmlImportTest1(XMldata XML)
GO


INSERT INTO ##XmlImportTest1(XmlData)

SELECT * 
FROM OPENROWSET(BULK '\\cespl-pc9\testing\Register.xml',
SINGLE_BLOB) AS x

SELECT t.u.value('field1[1]','datatype')
....
FROM ##XmlImportTest1 t
CROSS APPLY t.XMLData.nodes('path here')t(u)

make sure you replace lines in blue with correct values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-25 : 06:41:25

quote:
Originally posted by visakh16

you cant use # tables inside dynamic sql as it would be out of scope. you can use permanent table or ##tables instead

Of course you can use temporary tables within dynamic SQL!

CREATE TABLE	#SwePeso (i int)

INSERT	#SwePeso VALUES(1)

DECLARE	@SQL VARCHAR(MAX) = 'SELECT * FROM #SwePeso'

EXEC(@SQL)

N 56°04'39.26"
E 12°55'05.63"

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 06:44:35

CREATE TABLE ##XmlImportTest1(XMldata XML)
GO

INSERT INTO ##XmlImportTest1(XmlData)

SELECT *
FROM OPENROWSET(BULK '\\cespl-pc9\testing\Register.xml',
SINGLE_BLOB) AS x

Again i am getting error

SELECT t.u.value('RegId','int',
'FirstName','varchar(100)','LastName','varchar(100)','Email','varchar(100)','Password', 'bigint',
'SalespersonType ','varchar(100)','CreatedDate_dt ','datetime','UsetType','varchar(100)','SalesPerson_ID',' int','LoginCount', 'int'
)
FROM ##XmlImportTest1 t
CROSS APPLY t.XMLData.nodes('\\cespl-pc9\testing\Register.xml')t(u)
-----error--------
Msg 207, Level 16, State 1, Line 3
Invalid column name 'XmlData'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'XMLData'.
Msg 9506, Level 16, State 1, Line 9
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 07:43:11

you should give xpath inside nodes not xml file path value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-25 : 10:05:03

Thanks a lot Visakh i got result from ##XmlImportTest1 table.

i have 10000 rows in Table A. i process that deletes the 500 rows from Table A, that exports the data before deletion and stores the data in an xml file stored on the file system.now i am getting the deleted rows from table ##XmlImportTest1 by importing from xml file but What i wanting to be able to do is when i run a select statement against Table A, i want the result set to include the data from the xml or ##XmlImportTest1 file as well as the data from Table A.So now what i have to do ? please tell me

Thanks

Manju

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-25 : 13:18:39

sounds like instead of ACTUALLY deleting data and then moving it out, you should do a logical delete. Then you can see it any time you want.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:34:36

quote:
Originally posted by manju3606

Thanks a lot Visakh i got result from ##XmlImportTest1 table.

i have 10000 rows in Table A. i process that deletes the 500 rows from Table A, that exports the data before deletion and stores the data in an xml file stored on the file system.now i am getting the deleted rows from table ##XmlImportTest1 by importing from xml file but What i wanting to be able to do is when i run a select statement against Table A, i want the result set to include the data from the xml or ##XmlImportTest1 file as well as the data from Table A.So now what i have to do ? please tell me

Thanks

Manju

use a query like


SELECT columns
FROM YourActualTable
union all
select t.u.value('firstnode[1]','datatype') as field1,
t.u.value('secondnode[1]','datatype') as field2,
...
from yourimportedxmltable t
cross apply t.xmldata.nodes('your xml xpath')t(u)

the order of nodes should be same as order of columns coming from your first table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-26 : 06:08:26

i am getting error

SELECT t1.test
FROM b.test t1
union all
select t.u.value('xmlFileName','VARCHAR') as field1,
t.u.value('xml_data','XML') as field2
from #XmlImportTest t
cross apply t.xmldata.nodes('\\aaaaaa\testing\test.xml')t(u)

----------------Error--------------------
Msg 207, Level 16, State 1, Line 7
Invalid column name 'xmldata'.
Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 13:48:52

quote:
Originally posted by manju3606

i am getting error

SELECT t1.test
FROM b.test t1
union all
select t.u.value('xmlFileName','VARCHAR') as field1,
t.u.value('xml_data','XML') as field2
from #XmlImportTest t
cross apply t.xmldata.nodes('\\aaaaaa\testing\test.xml')t(u)

----------------Error--------------------
Msg 207, Level 16, State 1, Line 7
Invalid column name 'xmldata'.
Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Manju

please use as suggested
the path given in blue code should be xml xquery path not the xml file path.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 15:34:16

<bangs head on table>WHY????</bangs head on table>

Why not store the deleted data in a history table?

Do you think you are saving space with XML????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-26 : 19:26:43

quote:
Originally posted by DonAtWork

sounds like instead of ACTUALLY deleting data and then moving it out, you should do a logical delete. Then you can see it any time you want.

I agree with this unless you are dealing with such large amounts of data deletions where the reduction of records will have a noticeable impact to performance.

Can you please elaborate a little more on why you want to move the data out of the table? I am thinking since you selected XML files to hold this data that you are probably not dealing with millions of deleted records, so it seems like it may be a better idea to only flag the record as deleted.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-10-27 : 06:28:59

Hi Visakh,
I am using this script to import data from xml to sql table
CREATE TABLE dbo.Employee(
EmpId INT,
EmpName VARCHAR(50)
)
INSERT INTO Employee (EmpId, EmpName)
SELECT XmlQuery.Employee.query('EmpId').value('.', 'INT'),
XmlQuery.Employee.query('EmpName').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(XmlQuery AS XML)
FROM OPENROWSET(
BULK '\\aaaa-ppp9\testing\Employees.xml',
SINGLE_BLOB) AS T(XmlQuery)
) AS T(XmlQuery)
CROSS APPLY XmlQuery.nodes('Employees/Employee') AS XmlQuery(Employee);
The result is (0 row(s) affected) and i am getting two blank column when i run query against table employee like select * from Employee,
and one more thing that xml file has data.
Please tell me where i am going wrong

Thanks

Manju

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources