問題描述

我在變量@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')

這篇關于如何在sql中為xml的所有子節點添加屬性的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！