SQL杂记

1.SQL中直接处理xml数据类型，而不是将XML传入VARCHAR变量

 

/*
<TemplateData>
<ItemNumber></ItemNumber>
<Image1></Image1>
<Image2></Image2>
<Image3></Image3>
<Image4></Image4>
<Image5></Image5>
<Image6></Image6>
<Image7></Image7>
<Image8></Image8>
<Image9></Image9>
<Image10></Image10>
<Image11></Image11>
<Image12></Image12>
<Image13></Image13>
<Image14></Image14>
<Image15></Image15>
<Image16></Image16>
</TemplateData>
*/

SELECT TOP 1 @ItemNumber = @xml.value('(/TemplateData/ItemNumber/text())[1]','VARCHAR(20)'),
@Image1 = @xml.value('(/TemplateData/Image1/text())[1]', 'VARCHAR(2000)')

-- Add pictures
SELECT
T.C.value('.','nvarchar(2000)') AS ImageUrl
INTO #addList
FROM @xml.nodes('Data/*') AS T(C)
WHERE T.C.value('local-name(.)','char(5)') = 'Image'
AND T.C.value('.','nvarchar(2000)') NOT IN (SELECT ImageUrl FROM dbo.ProductImage WHERE ItemNumber = @ItemNumber )
AND T.C.value('.','nvarchar(2000)') <> @Image1

转载于:https://www.cnblogs.com/lyosaki88/p/4264309.html