Click here to Skip to main content
12,622,913 members (30,729 online)
Rate this:
 
Please Sign up or sign in to vote.
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>

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 20:01pm
Kunjammu1.1K
Updated 1-Jan-13 20:16pm
v2
Comments
arindamrudra 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161128.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2016
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