i have table with about 10000 rows,  there is a column named  Raw_XMLData is defined as varchar but data is xml format.  

try to insert into another table, if Raw_XMLData column has is valid xml data?

is it possible to do in T sql?

Thanks

V

yes, it should be possible.. try this sample below

data in table B is xml and data in table A is varchar

declare @a table(sname varchar(max))
Insert into @a(sname)
select (select name,dbid,crdate from master.dbo.sysdatabases
for XML Path('DatabaseDetails'))

declare @b table(Sname xml)
insert into @b
Select *  from @a

select * from @b

Hi Vaishu

Regarding your description, to achieve your requirement, you can reference the below sample.

DECLARE @varTbl TABLE (ID INT,col varchar(max))
INSERT INTO @varTbl 
SELECT 1,'<Person><name>Rose</name></Person>' --valid XML
UNION ALL
SELECT 2,'T<Person><name>Rose</name>' --invalid XML
UNION ALL
SELECT 3,'<name>Rose</name>' --valid XML

--For SQL Server 2012 or later
SELECT TRY_CONVERT(xml,col) from @varTbl
SELECT * FROM @varTbl WHERE TRY_CONVERT(xml,col) IS NOT NULL

--For SQL Server 2008
DECLARE MyCursor CURSOR FOR SELECT ID,col FROM @varTbl
DECLARE @varCol VARCHAR(MAX)
DECLARE @varID INT
DECLARE @tableInvalid TABLE (ID INT) -- This table to contain invliad XML IDs
OPEN MyCursor
FETCH NEXT FROM MyCursor 
INTO @varID,@varCol
WHILE @@FETCH_STATUS = 0
BEGIN
     BEGIN TRY
	 DECLARE @varXML XML
     SET @varXML=CAST(@varCol AS XML)
	 FETCH NEXT FROM MyCursor 
     INTO @varID,@varCol
	 END TRY
	 BEGIN CATCH
	 INSERT INTO @tableInvalid SELECT @varID --Insert invalid XML ID 
	 FETCH NEXT FROM MyCursor 
     INTO @varID,@varCol
	 END CATCH
END 
CLOSE MyCursor;
DEALLOCATE MyCursor;
SELECT * FROM @varTbl WHERE ID NOT IN(SELECT ID FROM @tableInvalid)
 

If you have any feedback on our support, you can click her