Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstraw
Starting Member
2 Posts |
 Posted - 2006-06-08 : 08:05:10
|
| I am new to SQL server - Would appreciate any help.I have a stored procedure (A) that creates a temp table with a single field of varchar(1000).I populate this table with string data and with contents of another table obtained via the use of a cursor.In a nutshell I am creating an XML document representation where each line of the temp table is a line of the xml document.At the end of the stored procedure I select all of the fields from the temp table.I have another stored procedure (B) that uses xp_sendemail and uses stored procedure (A) as the query for the attachment.The problem is that the attachment appears to contain all unicode characters and will not open in windows explorer. It will open in XML SPY and other XML editors. If I look at the data in a simple editor such as editplus, I see that each chracter is followed by a null character which is why I am assuming it is unicode.Any idea how to get the select output / email attachment to exclude these null characters? |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-06-08 : 09:37:05
|
| Have you considered using the FOR XML clause to pull the data out of the other table as XML (you have quite a lot of control over the output if you use the EXPLICIT keyword - look up FOR XML in BOL)? Doing it this way would get around having to use a cursor and it a bit neater than building up strings as you are doing.Mark |
 |
|
|
jstraw
Starting Member
2 Posts |
Posted - 2006-06-08 : 12:18:13
|
| In my research on this problem, I did find the FOR XML command. I think that this command may help to reduce the complexity of the Stored procedure. The problem is it only gives a partial XML, and it won't solve my bigger problem.My problem still remains that in the xp_sendemail, when I set my query to the stored procedure, it still gives me the information in what I believe is Unicode (each character is followed by a null). As a result, Internet Explore says the attachment is not a valid xml because the first line of the attachment is: <^?^x^m^l^ ^v^e^r^s^i^o^n^=^"^1^.^0^"^ ^?^?^>^ ....Where the ^ = a null (0x00).I don't know why this is happening or how to get the select or xp_sendemail stored procedure to generate an attachment that contains: <?xml version="1.0" ?> ......The requirements for this are strange and I am working with a legacy system in which the dll source code does not match the current system. Therefore, a sql approach is my only option until a re-write is compelete.Any help is appreciated. |
|
|
|
|
|
|
Problem with stored procedure output