如何在sql中为xml的所有子节点添加属性
我在变量@xml中有一个像下面这样的xml
I have a xml like below in a variable @xml
<ContentTemplate>
<Tab Title="Lesson">
<Section Title="Lesson Opening" />
<Section Title="Lesson/Activity" />
</Tab>
<Tab Title="Wrap Up and Assessment">
<Section Title="Lesson Closing" />
<Section Title="Tracking Progress/Daily Assessment" />
</Tab>
<Tab Title="Differentiated Instruction">
<Section Title="Strategies - Keyword" />
<Section Title="Strategies – Text" />
<Section Title="Resources" />
<Section Title="Acceleration/Enrichment" />
</Tab>
<Tab Title="District Resources">
<Section Title="Related Content Items" />
<Section Title="Other" />
</Tab>
</ContentTemplate>
我想为上述 xml 中的所有选项卡节点插入一个属性..输出应如下所示:
I want to insert an attribute for all tab nodes in the above xml.. the output should be like below:
<ContentTemplate>
<Tab Title="Lesson" PortletName="CommunitiesViewer">
<Section Title="Lesson Opening" />
<Section Title="Lesson/Activity" />
</Tab>
<Tab Title="Wrap Up and Assessment" PortletName="CommunitiesViewer">
<Section Title="Lesson Closing" />
<Section Title="Tracking Progress/Daily Assessment" />
</Tab>
<Tab Title="Differentiated Instruction" PortletName="CommunitiesViewer">
<Section Title="Strategies - Keyword" />
<Section Title="Strategies – Text" />
<Section Title="Resources" />
<Section Title="Acceleration/Enrichment" />
</Tab>
<Tab Title="District Resources" PortletName="CommunitiesViewer">
<Section Title="Related Content Items" />
<Section Title="Other" />
</Tab>
</ContentTemplate>
我尝试了下面的代码来得到上面的xml
i tried the following code to get the above xml
set @xml.modify( 'insert attribute PortletName {sql:variable("@PortletName")} into (ContentTemplate/Tab)[1]')
它只是更新第一个子节点.
its just update the first sub node.
如何更新xml的所有子节点..
how to update all the sub nodes of the xml..
提前致谢
推荐答案
变量中的 XML
DECLARE @xml XML=
N'<ContentTemplate>
<Tab Title="Lesson">
<Section Title="Lesson Opening" />
<Section Title="Lesson/Activity" />
</Tab>
<Tab Title="Wrap Up and Assessment">
<Section Title="Lesson Closing" />
<Section Title="Tracking Progress/Daily Assessment" />
</Tab>
<Tab Title="Differentiated Instruction">
<Section Title="Strategies - Keyword" />
<Section Title="Strategies – Text" />
<Section Title="Resources" />
<Section Title="Acceleration/Enrichment" />
</Tab>
<Tab Title="District Resources">
<Section Title="Related Content Items" />
<Section Title="Other" />
</Tab>
</ContentTemplate>';
1) FLWOR
.modify()
语句允许您更改 XML 中的一个合适的点,但是您需要多次调用才能更改许多位置.FLWOR 允许您重新构建 XML:
1) FLWOR
The .modify()
-statement allows you to change one decent point in your XML, but you'd need many calls to change many places. FLWOR allows you to re-build the XML out of itself:
SET @xml=@xml.query(
'<ContentTemplate>
{
for $t in /ContentTemplate/Tab
return
<Tab Title="{$t/@Title}" PortletName="CommunitiesViewer">
{$t/*}
</Tab>
}
</ContentTemplate>');
SELECT @xml
2) 使用 SELECT ... FOR XML PATH()
重建你会用这种方法达到同样的效果:再次重建 XML,但这次它被切碎并用作新的 SELECT ... FOR XML PATH
SELECT tb.value('@Title','nvarchar(max)') AS [@Title]
,'CommunitiesViewer' AS [@PortletName]
,tb.query('*')
FROM @xml.nodes('/ContentTemplate/Tab') AS A(tb)
FOR XML PATH('Tab'),ROOT('ContentTemplate')
相关文章