字符串转换成表

DECLARE @ModelsXml XML,@ids VARCHAR(100)
SET @ids='123@3,223@4,323@5'
set @ModelsXml=' <root> <v><aa>'+REPLACE(REPLACE(@ids,',','</bb></v><v><aa>'),'@','</aa><bb>')+'</bb> </v> </root>'
SELECT b.id,b.model,MM.quantity,b.PresentPrice AS price,b.Part AS description,b.[property]
FROM bmdproduct b
JOIN 
(
 SELECT c.v.value('./aa[1]','int') AS Id,c.v.value('./bb[1]','int') AS Quantity FROM @ModelsXml.nodes('/root/v')c(v)
)MM ON b.id=MM.Id

DECLARE @ModelsXml XML,@ids VARCHAR(100)
SET @ids='1/2/3'
set @ModelsXml='<v>'+REPLACE(@ids,'/','</v><v>')+'</v>'
SELECT bct.*, bct.CustomerID FROM bmdCustomerTel bct
JOIN
(SELECT c.v.value('.','varchar') AS Tel FROM @ModelsXml.nodes('/v')c(v))mm
ON bct.Tel=mm.tel

 

转载于:https://www.cnblogs.com/iewysdcwy/archive/2012/03/14/2396582.html