Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
i am converting a string to xml in sql stored procedure by using the following line
 
set @xml=convert(xml,N'<NewDataSet><Table1 Type="SUBJECT"><FromValue>ABILITY - FICTIONASDASDASDASDASD</FromValue><ToValue>Abominable snowman -
Fiction</ToValue></Table1></NewDataSet>')
 
but after executing the sp the result came in @xml is like below
 
<NewDataSet>
<Table1 Type="SUBJECT">
<FromValue>ABILITY - FICTIONASDASDASDASDASD</FromValue>
<ToValue>Abominable snowman -
Fiction</ToValue>
</Table1>
</NewDataSet>
 
Fiction is go to next line. because of this issue my storedprocedure not working properly. i want it to be in same line
Please help me to solve this issue
 
Thanks in Advance
Kunjammu
Posted 1-Jan-13 19:01pm
Edited 1-Jan-13 19:16pm
v2
Comments
arindamrudra at 2-Jan-13 4:29am
   
Is it a generic pattern for all the ToValue? Or only for this case you are getting this issue. I mean to say is it a data specific issue.
Kunjammu at 2-Jan-13 4:45am
   
yes. its a generic pattern for all the ToValue with two words with hiphen

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I can duplicate the result you state:
DECLARE @xml [xml]
set @xml=convert(xml,N'<newdataset><table1 type="SUBJECT"><fromvalue>ABILITY - 
FICTIONASDASDASDASDASD</fromvalue><tovalue>Abominable snowman - 
Fiction</tovalue></table1></newdataset>')
SELECT @xml
But I'm wondering if this is by accident; you mean to "paste":
DECLARE @xml [xml]
set @xml=convert(xml,N'<newdataset><table1 type="SUBJECT"><fromvalue>ABILITY - FICTIONASDASDASDASDASD</fromvalue><tovalue>Abominable snowman - Fiction</tovalue></table1></newdataset>')
SELECT @xml
Which looks the same, right? (Are you still with me?)
The thing is, the resulting xml for the CR containing bit (1st one) looks like:
<newdataset>
  <table1 type="SUBJECT">
    <fromvalue>ABILITY - 
FICTIONASDASDASDASDASD</fromvalue>
    <tovalue>Abominable snowman - 
Fiction</tovalue>
  </table1>
</newdataset>
And you're after (to my mind, the correct output):
<newdataset>
  <table1 type="SUBJECT">
    <fromvalue>ABILITY - FICTIONASDASDASDASDASD</fromvalue>
    <tovalue>Abominable snowman - Fiction</tovalue>
  </table1>
</newdataset>
What happens when you do this to your variable "@xml"?
SELECT REPLACE(CAST(@xml AS [nvarchar](MAX)),CHAR(13),'')
If this doesn't directly solve the problem, try playing around with the other indomitable CHAR(10) "linefeed". I suspect the stoproc problem stems from ...
something solvable by such a placement filter.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 480
1 Sergey Alexandrovich Kryukov 335
2 OriginalGriff 285
3 George Jonsson 246
4 Gihan Liyanage 235
0 OriginalGriff 4,873
1 CPallini 3,900
2 Sergey Alexandrovich Kryukov 3,474
3 George Jonsson 2,646
4 Gihan Liyanage 2,236


Advertise | Privacy | Mobile
Web04 | 2.8.140905.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100