March 16, 2011 at 2:15 pm
I'm trying to add '<?xml version="1.0" encoding="utf-8"?>'
to the results of this:
SELECT TOP 10 LOAN_ID
FROM TMSMTG AS LOAN
WHERE EFF_DT = '12/31/2010'
FOR XML AUTO, TYPE, ROOT('LoanData')
I've simplified my actual query. Initially, I tried to cast the select query to nvarchar(max), but I was getting truncation errors similar to what's posted in this thread: (http://www.sqlservercentral.com/Forums/Topic623774-21-1.aspx#bm705941).
Thanks for the help!
March 16, 2011 at 2:35 pm
See if this helps: http://msdn.microsoft.com/en-us/library/ms177400.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 3:02 pm
Since all XML is stored within SQL Server with utf-16 encoding the engine does not bother maintaining the XML declaration when you insert into an XML column (i.e. it drops it from your document) and provides no easy means to append it to your document when you select it because again, the encoding is implied.
Why do you need it?
"The XML 1.0 recommendation requires XML parsers to work without the XML declaration and interpret the byte-order mark instead and support UTF-16." (credit Jacob Sebastian)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 3:28 pm
GSquared - Tried that link. For some reason XMLNAMESPACES isn't being picked up as a keyword in the SSMS version that I'm using.
opc.three - The downstream system needs that tag in the file. And there is another header that is more company specific I need to add as well.
March 16, 2011 at 3:38 pm
OK, that's unfortunate. You're stuck either CONVERTing your XML to a Unicode string and pre-pending it with a declaration before it leaves the T-SQL context and delivering it to the caller as an NVARCHAR instead of an XML type...or having something outside SQL Server prepend your doc with a declaration (my preferred way to do this kind of thing).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 3:46 pm
I went down the road of trying to convert the xml to nvarchar(max) by following some google links, but when I set the xml to nvarchar(max) some of the xml gets truncated. So I can break up the xml into separate variables, but I'd like to hold off going that route.
Could SSIS (design script) be used to add the headers to the rest of the xml?
March 16, 2011 at 5:46 pm
rs80 (3/16/2011)
I went down the road of trying to convert the xml to nvarchar(max) by following some google links, but when I set the xml to nvarchar(max) some of the xml gets truncated. So I can break up the xml into separate variables, but I'd like to hold off going that route.Could SSIS (design script) be used to add the headers to the rest of the xml?
NVARCHAR(MAX) and XML types both have the same max length, a 2GB string, so the truncation must be happening in the SSIS layer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 5:46 pm
rs80 (3/16/2011)
I went down the road of trying to convert the xml to nvarchar(max) by following some google links, but when I set the xml to nvarchar(max) some of the xml gets truncated. So I can break up the xml into separate variables, but I'd like to hold off going that route.Could SSIS (design script) be used to add the headers to the rest of the xml?
NVARCHAR(MAX) and XML types both have the same max length, a 2GB string, so the truncation must be happening in the SSIS layer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 6:21 pm
I'm not using SSIS right now. I was asking if I could use it to add the xml headers.
For more information about the truncation you can search on google about converting xml to nvarchar(max) (http://www.sqlservercentral.com/Forums/Topic623774-21-1.aspx#bm705941). Many developers are facing this issue.
What I've understood is that before the query gets converted it to xml it needs to be saved into a nvarchar(max) variable.
March 16, 2011 at 9:16 pm
I think the forum thread you referenced is referring to client-driver data access issue...that has nothing to do with the internal representations of the XML or NVARCHAR(MAX) types within SQL Server and the fact that when casting from an XML type to an NVARCHAR(MAX) type data truncation will not occur.
Here is some sample code that may help you reach a workable solution:
DECLARE @xml XML ,
@xml_string NVARCHAR(MAX) ;
-- this should generate an XML doc larger than 8K on any system
SET @xml = ( SELECT TOP 4000
*
FROM master.sys.objects o1
CROSS JOIN master.sys.objects o2
FOR
XML PATH('') ,
ROOT('root')
) ;
-- let's convert the XML to an NVARCHAR(MAX)
SET @xml_string = CONVERT(NVARCHAR(MAX), @xml) ;
-- show that we have not lost any characters in the conversion process
SELECT LEN(@xml_string) AS xml_length ;
SELECT '<?xml version="1.0" encoding="utf-8"?>' + @xml_string AS [xml document + declaration as NVARCHAR(MAX)] ;
A couple notes:
- when running the above code in SSMS you will see that the XML output is truncated...that is a client-driver data access issue 🙂 SSMS will only allow you to output the first 65K bytes of a string delivered to the "Results to Grid" output window and 8K when in "Results to Text" (Tools > Options > Query Results > SQL Server > Results to Grid/Text). When consuming the data be careful of it so you do not have truncated data. A .NET string should do just fine, that's what SSIS uses under the covers.
- in case your downstream consumer expects a byte-order mark (BOM), know that when casting from XML to NVARCHAR(MAX) SQL Server drops the UTF-16 BOM (reference)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 17, 2011 at 7:55 am
Pretty cool stuff...
So I think what you're saying is that even though SSMS has the limitation of truncating the XML when showing the results in grid or text (what you're referring to as client-driver data access issue), the XML will not be truncated when I call this SP through a SSIS package (for example). Please let me know if I've understood correctly.
I also had tried to save the results to a file, but again the xml was truncated.
March 17, 2011 at 9:24 am
rs80 (3/17/2011)
Pretty cool stuff...So I think what you're saying is that even though SSMS has the limitation of truncating the XML when showing the results in grid or text (what you're referring to as client-driver data access issue), the XML will not be truncated when I call this SP through a SSIS package (for example). Please let me know if I've understood correctly.
I also had tried to save the results to a file, but again the xml was truncated.
I should have written it as "client-driver/client-data-access issue". Some non-Microsoft drivers (e.g. 3rd party JDBC, ODBC for Linux, FreeTDS, etc.) may not be able to keep up with the MAX and XML data types so those would be a "client-driver issue". The Microsoft SQL Server Native Client Driver does not have any limitations when it comes to the MAX and XML data types...i.e. the Native Client can retrieve a resultset containing a 2GB NVARCHAR(MAX) without a problem...that said, SSMS "protects" the client computer by limiting the amount of data that can be retrieved in an XML or string column...that would be a "client-data-access issue". Imagine a resultset containing 500 rows containing an NVARCHAR(MAX) column that averaged 1GB in size...I do not know of many desktops or laptops (or servers for that matter) that could handle it if SSMS were to go ahead and take-on the entire resultset 😉
Back to your solution....you are correct in your understanding. SSIS will not truncate any output on your behalf like SSMS. If you're going with the "declaration + XML converted to NVARCHAR(MAX) in SQL" solution then you can use an ADO.NET or OLEDB data source from SSIS to SQL Server to consume the NVARCHAR(MAX) as a Unicode string. If you're going to pull the XML type out of SQL Server and then work on it in SSIS (what I would recommend) then you may be forced to use an ADO.NET data source in SSIS to get the data from SQL Server into an SSIS variable. I do not know for sure, it may be possible with an SSIS OLEDB data source, but I think there are issues with it consuming an XML column so I would start with an ADO.NET data source.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 17, 2011 at 3:16 pm
Understood the point in your first paragraph on a high level 🙂
Understood your second point the hard way. Initially, I had the OLEDbConnection for ExecuteSQLTask and after trying many different ways I was getting the error: MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"? Then I read your post again and I realized you were saying to use ADOConnection for the ExecuteSQLTask and it's working now.
How did you know that?
March 17, 2011 at 3:36 pm
I was just helping someone on [what I thought was] an unrelated issue last week and their issue turned out to be the OLE DB data source 😀
http://www.sqlservercentral.com/Forums/Topic1073400-148-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 18, 2011 at 12:45 pm
Thanks for the help opc.three. I've been able to create the XML file with the headers I need.
I do have to ask why doesn't oledb connection work and ado.net work. What's being done behind the scenes with the ADO.NET connection. Is SSIS storing the data in an ADO.NET dataset (for example)?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply