我有这个xml,我想为变量中指定的/COUNTRY/DisplayValue
插入<TICKED>
节点和<DISABLED>
节点,但前提是<TICKED>
节点还不存在。
DECLARE @xml AS XML;
SET @xml = '
<COUNTRY TICKOPTION="Y">
<DisplayValue>IRELAND</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[IRELAND]</ReportValue>
<TICKED>Y</TICKED>
</COUNTRY>
<COUNTRY TICKOPTION="Y">
<DisplayValue>CANADA</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[CANADA]</ReportValue>
<TICKED>Y</TICKED>
</COUNTRY>
<COUNTRY TICKOPTION="Y">
<DisplayValue>INDIA</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[INDIA]</ReportValue>
<TICKED>Y</TICKED>
</COUNTRY>'
DECLARE @HC_Country_DV AS VARCHAR(255);
SET @HC_Country_DV = 'CANADA'
SET @xml.modify('
insert (<TICKED>Y</TICKED>,<DISABLED>Y</DISABLED>)
into (/COUNTRY[DisplayValue = sql:variable("@HC_Country_DV")])[1]')
SELECT @xml
当我这样做时,我会得到两个<TICKED>
条目
<COUNTRY TICKOPTION="Y">
<DisplayValue>IRELAND</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[IRELAND]</ReportValue>
<TICKED>Y</TICKED>
</COUNTRY>
<COUNTRY TICKOPTION="Y">
<DisplayValue>CANADA</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[CANADA]</ReportValue>
<TICKED>Y</TICKED>
<TICKED>Y</TICKED>
<DISABLED>Y</DISABLED>
</COUNTRY>
<COUNTRY TICKOPTION="Y">
<DisplayValue>INDIA</DisplayValue>
<ReportValue>[Hierarchy].[CIMSHierarchy].[Country Name].&[INDIA]</ReportValue>
<TICKED>Y</TICKED>
</COUNTRY>
我不太明白如何只插入<TICKED>
节点一次,我在modify中玩了"if"条件,但不明白。我考虑过用"删除"的修改方法来删除一个,但我想我会在这里问一下,看看你们是否能再次帮助我。。。
但仅当CCD_ 7节点还不存在时。
添加对TICKED
节点的检查。and not(TICKED)
。
SET @xml.modify('
insert (<TICKED>Y</TICKED>,<DISABLED>Y</DISABLED>)
into (/COUNTRY[DisplayValue = sql:variable("@HC_Country_DV") and not(TICKED)])[1]')