converting XML 1

[lfc77]

I have a field in one of my tables that I am writing a string of XML to. Is it possible to convert this XML using a SELECT statement or some other means?

Any help would be really appreciated.


Cheers,

lfc77

 

[hmckillop]

You would need to know a bit about the XML structure, but you could do something like this

--declare a string to hold you xml from your table
DECLARE @v_XMLString VARCHAR(2000)
--replace proper fieldname and tablename
SELECT @v_XMLSTring = xmlfield FROM MyTable

--Open File handle to read  XML
DECLARE @FileHandle INT -- Filehandle to the xml stuff 

--Execute the inbuilt XML stored proc handler
EXEC dbo.sp_xml_preparedocument @FileHandle output, @v_XMLSTring 
SELECT 
	*
FROM
	OPENXML(@FileHandle, 'root/noderoot/node', 1 ) 
	WITH (	field1       	Int         	'@attribute1',
		field2 		Int 		'@attribute2')

EXEC dbo.sp_xml_removedocument @FileHandle

Hope this points you in the right direction

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[lfc77]

Thanks hmckillop!


Cheers,

lfc77