选择后在xml中更新位置索引(Updating position index in xml after select)

选择后在xml中更新位置索引(Updating position index in xml after select)

我有以下xml，当在最多三个组中选择时，需要修改其索引值以反映其在返回的xml中的新位置。 我已经在这篇文章的底部写下了我需要的结果。

<root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> <document> <index>4</index> <text>stuff</text> </document> <document> <index>5</index> <text>stuff</text> </document> <document> <index>6</index> <text>stuff</text> </document> <document> <index>7</index> <text>stuff</text> </document> <document> <index>8</index> <text>stuff</text> </document> <document> <index>9</index> <text>stuff</text> </document> <document> <index>10</index> <text>stuff</text> </document> </root>

最多提取三组后。

<root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> </root

I have the following xml that when selected in groups of at most three needs to have their index value modified to reflect its new position in the returned xml. I have wrote out what result I need at the bottom of this post.

<root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> <document> <index>4</index> <text>stuff</text> </document> <document> <index>5</index> <text>stuff</text> </document> <document> <index>6</index> <text>stuff</text> </document> <document> <index>7</index> <text>stuff</text> </document> <document> <index>8</index> <text>stuff</text> </document> <document> <index>9</index> <text>stuff</text> </document> <document> <index>10</index> <text>stuff</text> </document> </root>

After extracting by a maximum of groups of three.

<root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> <document> <index>2</index> <text>stuff</text> </document> <document> <index>3</index> <text>stuff</text> </document> </root <root> <document> <index>1</index> <text>stuff</text> </document> </root

最满意答案

将查询返回的XML存储在变量中并更新索引值。

set @XMLTarget.modify('replace value of (/root/document/index/text())[1] with 1') set @XMLTarget.modify('replace value of (/root/document/index/text())[2] with 2') set @XMLTarget.modify('replace value of (/root/document/index/text())[3] with 3')

SQL小提琴

Store the XML returned by your query in a variable and update the index value.

set @XMLTarget.modify('replace value of (/root/document/index/text())[1] with 1') set @XMLTarget.modify('replace value of (/root/document/index/text())[2] with 2') set @XMLTarget.modify('replace value of (/root/document/index/text())[3] with 3')

SQL Fiddle