如何从sql中的xml中删除换行符 [英] How to remove newline from xml in sql

问题描述

我正在使用以下行将字符串转换为sql存储过程中的xml

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>')

但在执行sp之后，@xml中的结果如下所示



< NewDataSet>

< Table1 Type =SUBJECT>

< FromValue> ABILITY - FICTIONASDASDASDASDASD< / FromValue>

< ToValue>令人憎恶的雪人 -

小说< / ToValue>

< / Table1>

< / NewDataSet>



小说转到下一行。因为这个问题我的storedprocedure不能正常工作。我想要它在同一行

请帮我解决这个问题



提前致谢

Kunjammu

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

推荐答案

我可以复制你所说的结果：

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

哪个看起来一样，对吧？ （你还和我在一起吗？）

问题是，包含CR（第一个）的CR的结果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>

对变量@xml执行此操作会发生什么情况？

What happens when you do this to your variable "@xml"?

SELECT REPLACE(CAST(@xml AS [nvarchar](MAX)),CHAR(13),'''')

如果这不直接解决问题，尝试与其他不屈不挠的CHAR（10）换行一起玩。我怀疑stoproc问题源于......

这样的贴片过滤器可以解决这个问题。

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.

这篇关于如何从sql中的xml中删除换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！