Obtaining data from an XML column to a document

16 Apr 2009 10:21 AM

Paul W

Sorry if a solution exists for this but I have tried searching and I cannot
find anything.
I am currently playing with the XML data type in SQL Server 2005 SP2. I am
actually trying to generate some XML based on various data and then store it
in the XML column. It all appears to be fine.

However, I want to validate that everything has been generated and stored
correctly but I just cannot seem to get the data out now.
Ideally I could export the data to a document which I can then open and
read. Query Analyzer truncates the output, even if I save the results to a
file.
So, is there a simple way to export the data from an XML column straight to
a document of some sort?
Thanks in advance,
Paul

16 Apr 2009 2:44 PM

Bob Beauchemin

Hi Paul,

Do you mean get the data from the database and save it to a file? I'd use a
SQLCLR procedure or function for that. Remember the data is now on the
database server, you wouldn't be saving it to a file on the end-user's
desktop (unless the server has file system access to the end-user's desktop,
and the person running the proc has write permission).

Cheers,
Bob Beauchemin
SQLskills

Show quoteHide quote

"Paul W" <Pa***@discussions.microsoft.com> wrote in message
news:17036612-336B-4E1F-9E10-4E32BEA11FF2@microsoft.com...
> Sorry if a solution exists for this but I have tried searching and I
> cannot
> find anything.
> I am currently playing with the XML data type in SQL Server 2005 SP2. I am
> actually trying to generate some XML based on various data and then store
> it
> in the XML column. It all appears to be fine.
>
> However, I want to validate that everything has been generated and stored
> correctly but I just cannot seem to get the data out now.
> Ideally I could export the data to a document which I can then open and
> read. Query Analyzer truncates the output, even if I save the results to a
> file.
> So, is there a simple way to export the data from an XML column straight
> to
> a document of some sort?
> Thanks in advance,
> Paul

17 Apr 2009 6:32 AM

Bob

You can use bcp queryout with a stored procedure that returns the XML:

bcp "EXEC your_proc" queryout c:\temp\tmp.xml -Syour_server -T -c

This will put the XML into a file without truncating it. In SQL 2005 you
can also query the XML data-type and return data in tabular format.

You may also be able to use SSIS to do the export.

Is this the kind of thing you are after?

HTH
wBob

Show quoteHide quote

"Paul W" wrote:

> Sorry if a solution exists for this but I have tried searching and I cannot
> find anything.
> I am currently playing with the XML data type in SQL Server 2005 SP2. I am
> actually trying to generate some XML based on various data and then store it
> in the XML column. It all appears to be fine.
>
> However, I want to validate that everything has been generated and stored
> correctly but I just cannot seem to get the data out now.
> Ideally I could export the data to a document which I can then open and
> read. Query Analyzer truncates the output, even if I save the results to a
> file.
> So, is there a simple way to export the data from an XML column straight to
> a document of some sort?
> Thanks in advance,
> Paul

17 Apr 2009 9:30 AM

Paul W

Hello,

Thanks to both for your replies. Firstly I do mean that I just wish to
obtain the data from the database and save to a file. I realise the issues
with the data being on the database server not the desktop but as I say this
is purely for me to validate the XML I have generated is correct.

Secondly (Bob, not Bob Beauchemin), do you mean simply create a stored
procedure that returns the data in the XML column and then use the bcp... as
you suggest? Is it that simple? It is exactly what I am after so I will give
it a try.

Thanks again to both!

17 Apr 2009 9:57 AM

Paul W

Just thought I would let you know I tried this and it worked perfectly -
exactly what I was trying to do.

Thanks again!

Paul