Post reply

Retrieve XML Data as String

  • June 23, 2009 at 11:25 am

    #73225

    Hi, I'm having a little problem writing a query against a table storing an XML data packet.

    Background: We have a sales application that processes an XML packet sent to us by a partner. Once it is processed, we stick the entire XML received into an XML data-type column in our history table.

    I've been asked to generate a report with 200 samples of the XML data as received from our partner. I can't figure out how to export the samples. Everything I've tried seems to truncate the data. If I simply select (SELECT TOP 10 [XMLData] FROM TransactionHistory;) to a grid, then I get a nice blue link in the grid that I can click on and get the entire XML in another tab (SSMS). Is there any way to get this output to a file without truncating it?

    Thanks for any help.

  • June 23, 2009 at 11:42 am

    #1014140

    Well, when you think you know it all, you suddenly realize you are standing at the last position in the queue:

    good Question: which prompted me to search around:

    http://www.sqldbatips.com/showarticle.asp?ID=23

    or use FOR XML PATH statement.. check BOL

    Maninder
    www.dbanation.com

  • June 23, 2009 at 11:57 am

    #1014150

    I started playing around with the FOR XML, but the problem is that it cuts the data(string?) off at 256 characters. I need to get the whole thing. This is pretty frustrating. I've also tried CASTing to a varchar(max), but that stops at 256 characters also.

  • June 23, 2009 at 12:01 pm

    #1014155

    Please provide some sample data together with your expected result.

    For posting xml data please use [ code="xml" ] tag (with spaces removed) around your sample data.

    Other than that, please see the link in my signature on how to provide data.

    You could also search for "XML Workshop" on this forum. This will give you a list of articles by J. Sebastian covering almost every subject related to xml.

    Edit: addtl. note: the string might be cut after 256 chars due to your settings of SSMS. If you're in text mode for your result set and using SSMS standard setting data will be truncated after 256 character. Try this: Goto Tool-> Options->Query Results->SQL Server->Results to text and change the value.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 23, 2009 at 1:44 pm

    #1014231

    Hi Lutz,

    It looks like the addtl. note is actually what I needed the most. I can get a maximum of 8192 characters with that, which is enough to satisfy the request (needed by end of day of course). Thanks for the help!:-)

  • June 23, 2009 at 2:05 pm

    #1014245

    You're welcome.

    I just did remember a different thread with a similar question... 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 23, 2009 at 2:58 pm

    #1014277

    Gary Hines (6/23/2009)

    Hi, I'm having a little problem writing a query against a table storing an XML data packet.

    Background: We have a sales application that processes an XML packet sent to us by a partner. Once it is processed, we stick the entire XML received into an XML data-type column in our history table.

    I've been asked to generate a report with 200 samples of the XML data as received from our partner. I can't figure out how to export the samples. Everything I've tried seems to truncate the data. If I simply select (SELECT TOP 10 [XMLData] FROM TransactionHistory;) to a grid, then I get a nice blue link in the grid that I can click on and get the entire XML in another tab (SSMS). Is there any way to get this output to a file without truncating it?

    Thanks for any help.

    See if this does what you need.

    exec sp_configure 'show',1

    reconfigure

    exec sp_configure 'ole',1

    reconfigure

    GO

    declare @test-2 table (RowID INT IDENTITY PRIMARY KEY CLUSTERED, Data XML)

    insert into @test-2 (Data) values ('12')

    declare @Tmp varchar(max), @FileName varchar(200)

    set @Tmp = convert(varchar(max), (select Data from @test-2 where RowID = 1))

    set @FileName = 'C:\SQL\MyOutputFile.xml'

    declare @FSO int, @TSO int

    execute master..sp_OACreate 'Scripting.FileSystemObject', @FSO output

    execute master..sp_OAMethod @FSO, 'CreateTextFile', @TSO output, @FileName

    execute master..sp_OAMethod @TSO, 'Write', null, @Tmp

    execute master..sp_OAMethod @TSO, 'Close'

    execute master..sp_OADestroy @TSO

    execute master..sp_OADestroy @FSO

    exec sp_configure 'ole',0

    reconfigure

    exec sp_configure 'show',0

    reconfigure

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2009 at 3:03 pm

    #1014281

    Gary Hines (6/23/2009)

    Hi, I'm having a little problem writing a query against a table storing an XML data packet.

    Background: We have a sales application that processes an XML packet sent to us by a partner. Once it is processed, we stick the entire XML received into an XML data-type column in our history table.

    I've been asked to generate a report with 200 samples of the XML data as received from our partner. I can't figure out how to export the samples. Everything I've tried seems to truncate the data. If I simply select (SELECT TOP 10 [XMLData] FROM TransactionHistory;) to a grid, then I get a nice blue link in the grid that I can click on and get the entire XML in another tab (SSMS). Is there any way to get this output to a file without truncating it?

    Thanks for any help.

    See if this helps:

    exec sp_configure 'show',1

    reconfigure

    exec sp_configure 'ole',1

    reconfigure

    GO

    declare @test-2 table (RowID INT IDENTITY PRIMARY KEY CLUSTERED, Data XML)

    insert into @test-2 (Data) values ('12')

    declare @Tmp varchar(max), @FileName varchar(200)

    set @Tmp = convert(varchar(max), (select Data from @test-2 where RowID = 1))

    set @FileName = 'C:\SQL\MyOutputFile.xml'

    declare @FSO int, @TSO int

    execute master..sp_OACreate 'Scripting.FileSystemObject', @FSO output

    execute master..sp_OAMethod @FSO, 'CreateTextFile', @TSO output, @FileName

    execute master..sp_OAMethod @TSO, 'Write', null, @Tmp

    execute master..sp_OAMethod @TSO, 'Close'

    execute master..sp_OADestroy @TSO

    execute master..sp_OADestroy @FSO

    exec sp_configure 'ole',0

    reconfigure

    exec sp_configure 'show',0

    reconfigure

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2009 at 3:14 pm

    #1014293

    @waynes

    These OLE thinks are the reason why I'm really glad about the CLR integration in SQL Server 2005 and above.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply