如何将ID分配给同一级别的xml节点

我正在尝试修改第三方xml,以便所有元素都具有来自T-sql的Id.

这是原始的xml(部分)：

<Tables>
   <Table Type="LineItem">
      <TableRow>
        <Field Name="LI_NominalCode" Type="wd_lit_nominalcode">244234</Field>
        <Field Name="LI_NominalDesc" Type="lit_nominaldesc">RENT RECEIVABLE - INTERNAL</Field>
        <Field Name="LI_Account" Type="lit_wd_account" />
        <Field Name="LI_AccountDesc" Type="lit_wd_accountdesc" />
        <Field Name="LI_SecondAccount" Type="lit_wd_2ndaccount" />
        <Field Name="LI_SecondAccountDesc" Type="lit_wd_2ndaccountdesc" />
        <Field Name="LI_NetValue" Type="lit_vatexcludedamount">4522.89</Field>
        <Field Name="LI_EnergyUsage" Type="wd_energyusage">56666</Field>
        <Field Name="LI_EnergyType" Type="wd_energytype">ELECTRICITY</Field>
      </TableRow>
      <TableRow>
        <Field Name="LI_NominalCode" Type="wd_lit_nominalcode">150021</Field>
        <Field Name="LI_NominalDesc" Type="lit_nominaldesc">Rent Building 1</Field>
        <Field Name="LI_Account" Type="lit_wd_account" />
        <Field Name="LI_AccountDesc" Type="lit_wd_accountdesc" />
        <Field Name="LI_SecondAccount" Type="lit_wd_2ndaccount" />
        <Field Name="LI_SecondAccountDesc" Type="lit_wd_2ndaccountdesc" />
        <Field Name="LI_NetValue" Type="lit_vatexcludedamount">456.37</Field>
        <Field Name="LI_EnergyUsage" Type="wd_energyusage">2805.00</Field>
        <Field Name="LI_EnergyType" Type="wd_energytype">ELECTRICITY</Field>
      </TableRow>
      <TableRow>
        <Field Name="LI_NominalCode" Type="wd_lit_nominalcode">2342341</Field>
        <Field Name="LI_NominalDesc" Type="lit_nominaldesc">Rent Building 2</Field>
        <Field Name="LI_Account" Type="lit_wd_account" />
        <Field Name="LI_AccountDesc" Type="lit_wd_accountdesc" />
        <Field Name="LI_SecondAccount" Type="lit_wd_2ndaccount" />
        <Field Name="LI_SecondAccountDesc" Type="lit_wd_2ndaccountdesc" />
        <Field Name="LI_NetValue" Type="lit_vatexcludedamount">355</Field>
        <Field Name="LI_EnergyUsage" Type="wd_energyusage">6900</Field>
        <Field Name="LI_EnergyType" Type="wd_energytype">ELECTRICITY</Field>
      </TableRow>
    </Table>
    <Table Type="BankAccountTable" />
    <Table Type="VATTable" />
  </Tables>

如您所见,<表>元素没有Id,所以后来在这个过程中难以识别它们.

我想创建一个循环来遍历所有< Table>元素并执行如下代码片段：

set @xml.modify('
insert attribute ID {sql:variable("@idString")}
into (/Documents/Document/Invoice/Tables/Table[@Type="LineItem"]/TableRow)[sql:variable("@id")]')

问题出在最后一个sql变量上,错误是：

XQuery [modify()]: Only ‘http://www.w3.org/2001/XMLSchema#decimal?’,
‘http://www.w3.org/2001/XMLSchema#boolean?’ or ‘node()*’ expressions
allowed as predicates,found ‘xs:string ?’

这工作正常,但我不想总是改变同一行(数字1).

set @xml.modify('
insert attribute ID {sql:variable("@idString")}
into (/Documents/Document/Invoice/Tables/Table[@Type="LineItem"]/TableRow)[1]')

顺便说一句,如果我使用int变量而不是字符串：

set @xml.modify('insert attribute ID {sql:variable("@idString")} 
                 into (/Documents/Document/Invoice/Tables/Table[@Type="LineItem"]/TableRow)[sql:variab‌le("@idInt")]')`

我收到另一个错误：

XQuery [modify()]: The target of ‘insert’ must be a single node,found
‘element(TableRow,xdt:untyped) *

您可以将变量@id与函数position()进行比较.

declare @id int
declare @RowCount int
select @RowCount = @xml.value('count(/Tables/Table[@Type="LineItem"]/TableRow)','int')

set @id = 1

while @id <= @RowCount
begin
  set @xml.modify('
    insert attribute ID {sql:variable("@id")}
    into (/Tables/Table[@Type="LineItem"]/TableRow[position()=sql:variable("@id")])[1]')

  set @id = @id + 1
end

如何将ID分配给同一级别的xml节点

猜你在找的XML相关文章