I'm writing one stored procedure, which I have to create a xml column from db.
µ = CHAR(181)
this is value separato开发者_开发技巧r,
¶ = CHAR(182)
this is row separator
This is the statement I wrote. I know its not well formed.
SELECT @xmlString= CAST('<root><Section> ID =' + REPLACE(REPLACE ('20211µ1¶20212µ2', CHAR(182),
'</Section><Section> ID ='),CHAR(181), ' Slno=') + '</Section></root>' AS XML)
This is the pattern which I need to display like this.
<root>
<sections id="20211" slno="1" ></sections>
<sections id="20215" slno="2" ></sections>
</root>
declare @s varchar(50) = '20211µ1¶20212µ2'
declare @xmlString xml
;with C as
(
select T.N.value('value[1]', 'int') as id,
T.N.value('value[2]', 'int') as slno
from (select cast('<item><value>'+replace(replace(@s, 'µ','</value><value>'), '¶','</value></item><item><value>')+'</value></item>' as xml)) as X(XMLCol)
cross apply X.XMLCol.nodes('item') as T(N)
)
select @xmlString =
(
select C.id as [@id] ,
C.slno as [@slno]
from C
for xml path('sections'), root('root'), type
)
select @xmlString
Result:
<root>
<sections id="20211" slno="1" />
<sections id="20212" slno="2" />
</root>
精彩评论