How to extract an XML value from a SQL Server Table
When you use an XML field in a Database Table, there are times when you want to View the XML but are unable to successfully view it in Sql Server Management Studio because the format is wrong. Alternatively, the text field is too long to be displayed in a cell. You aren't able to copy the contents to view, the only way is to write it to a file to view or crete a C# program to extract. The following example will show you how to extract the contents of the file and write it to a file.
Before you start, you will need to turn on features in SQL to enable to happen, this can be done with the following commands.
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'Ole Automation Procedures', 1;
go
reconfigure;
go
The next steps is to create an object to exract the data and the following code shows how it is done.
-- The following few lines are just my example of retrieving the data and putting in to a variable,
Declare @VarOut nvarchar(max)
Select @VarOut = cast(FXml as nvarchar(max)) from ExXML where ID = 1
Print caST(@VarOut AS NVARCHAR(MAX))
-- Declare the variables that you will need to use in the operation,Declare @OLE int
declare @ObjectToken INT
DECLARE @FId int
declare @File varchar(200) = 'C:\temp\Sitemap.xml'
-- The actual nuts and bolts of the operation.
EXECUTE sp_OACreate 'Scripting.FileSystemObject',@OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileId OUT, @File,2,1
EXECUTE sp_OAMethod @FId, 'WriteLine', Null, @VarOut;
EXECUTE sp_OAMethod @FId, 'Close';
EXECUTE sp_OADestroy @FId OUT;
EXECUTE sp_OADestroy @OLE OUT;
If you are trying to extract a binary file stored in an Image field, then you should refer to the previous article as we use a different method there.
Last Modified : June 2023