问题描述
我在变量@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')