請問一下,我下的指令如下,但出來的結果會將@轉成_x0040_而沒有產生它原有的功能。請問這要怎麼調整編碼使@有功能?
select name AS '@ABC',sex
from member FOR XML RAW('RAW'),ROOT('ROOT'),ELEMENTS
<ROOT>
<RAW>
<_x0040_ABC>jacky </_x0040_ABC>
<sex>m </sex>
</RAW>
<RAW>
<_x0040_ABC>ada </_x0040_ABC>
<sex>w </sex>
</RAW>
<RAW>
<_x0040_ABC>able </_x0040_ABC>
<sex>m </sex>
</RAW>
<RAW>
<_x0040_ABC>colbel </_x0040_ABC>
<sex>m </sex>
</RAW>
</ROOT>
後來找了很久才知道格式寫錯了,應該向下面這樣。
select ID as [@category-id]
from Catalog
FOR XML PATH('category'),ROOT('catalog
如圖:
另外寫了一份文件,有需要的話可以參考:
https://hackmd.io/@Y00YB1ZGRJaUT9hlzSidIw/rkXGeVrwv
那就手動Replace回去吧~
declare @member table(
name nvarchar(50)
,sex varchar(1)
)
insert into @member
values('jacky','m')
,('ada','w')
,('able','m')
,('colbel','m')
declare @Txt nvarchar(max) = ''
select @Txt = (
select name AS '@ABC'
,sex
from @member
FOR XML RAW('RAW')
,ROOT('ROOT')
,ELEMENTS
)
select Replace(@Txt,'_x0040_','@') XML_Txt