CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
b1gb3n97
b1gb3n97

Reputation: 21

XQuery - iterate over every attribute

I am trying to iterate over XML with this structure:

<Query id="Y120-M2-D1-H8-M42-S12-MS276-6676" name="Transactions_ModelGenerator_v_1.0.0.104">
  <Description />
  <Columns>
    <Column caption="Client Code" sorting="None" sortIndex="-1">
      <Expr class="ENTATTR" id="c.Number" />
    </Column>
    <Column caption="Client Name" sorting="None" sortIndex="-1">
      <Expr class="ENTATTR" id="c.Name" />
    </Column>
    <Column caption="Market Code" sorting="None" sortIndex="-1">
      <Expr class="ENTATTR" id="r.Number" />
    </Column>
    <Column caption="Market Name" sorting="None" sortIndex="-1">
      <Expr class="ENTATTR" id="r.Name" />
    </Column>
  </Columns>
  <JustSortedColumns />
  <Conditions linking="All">
    <Condition class="SMPL" enabled="True" readOnly="False">
      <Operator id="Equal" />
      <Expressions>
        <Expr class="ENTATTR" id="c.Number" />
        <Expr class="CONST" type="String" kind="Scalar" value="A1B" text="A1B" />
      </Expressions>
    </Condition>
    <Condition class="SMPL" enabled="True" readOnly="False">
      <Operator id="Equal" />
      <Expressions>
        <Expr class="ENTATTR" id="ConversionCurrency" />
        <Expr class="CONST" type="String" kind="Scalar" value="EUR" text="EUR" />
      </Expressions>
    </Condition>
  </Conditions>
</Query>

I want to loop through the Columns, and replace certain keywords, specifically the caption attribute. For example, I want to replace keyword "Client" with "Cedent".

I've tried writing something like this:

update #data
set data.modify('
    for $col in /Query/Columns[1]/Column/caption
    replace value of $col with fn:replace($col, "Client", "Cedent")
    where fn:contains($col, "Client")
')

But of course I'm getting errors I'm not sure how to work around:

XQuery [#data.data.modify()]: Syntax error near 'replace', expected 'where', '(stable) order by' or 'return'.

I'm not very familiar with XQuery. Can somebody help me here?

Upvotes: 2

Views: 474

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

   for $col in /Query/Columns[1]/Column/@caption[fn:contains(., "Client")]
   let $x := fn:replace($col, "Client", "Cedent")
   return replace value of node $col with $x

Upvotes: 2

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22182

Unfortunately, .modify() method can update just one value at a time.

Very ugly way due to MS SQL Server lack of support of the latest w3c standards for XQuery, Xpath, etc.

SQL

DECLARE @xml XML = N'<Query id="Y120-M2-D1-H8-M42-S12-MS276-6676" name="Transactions_ModelGenerator_v_1.0.0.104">
    <Description/>
    <Columns>
        <Column caption="Client Code" sorting="None" sortIndex="-1">
            <Expr class="ENTATTR" id="c.Number"/>
        </Column>
        <Column caption="Client Name" sorting="None" sortIndex="-1">
            <Expr class="ENTATTR" id="c.Name"/>
        </Column>
        <Column caption="Market Code" sorting="None" sortIndex="-1">
            <Expr class="ENTATTR" id="r.Number"/>
        </Column>
        <Column caption="Market Name" sorting="None" sortIndex="-1">
            <Expr class="ENTATTR" id="r.Name"/>
        </Column>
    </Columns>
    <JustSortedColumns/>
    <Conditions linking="All">
        <Condition class="SMPL" enabled="True" readOnly="False">
            <Operator id="Equal"/>
            <Expressions>
                <Expr class="ENTATTR" id="c.Number"/>
                <Expr class="CONST" type="String" kind="Scalar" value="A1B" text="A1B"/>
            </Expressions>
        </Condition>
        <Condition class="SMPL" enabled="True" readOnly="False">
            <Operator id="Equal"/>
            <Expressions>
                <Expr class="ENTATTR" id="ConversionCurrency"/>
                <Expr class="CONST" type="String" kind="Scalar" value="EUR" text="EUR"/>
            </Expressions>
        </Condition>
    </Conditions>
</Query>';

DECLARE @oldValue VARCHAR(10) = 'Client'
    , @NewValue VARCHAR(10) = 'Cedent'
    , @caption VARCHAR(30);

WHILE @xml.exist('/Query/Columns/Column[fn:contains(@caption, sql:variable("@oldValue"))]') = 1
BEGIN
    SET @caption = @xml.value('(/Query/Columns/Column[fn:contains(@caption, sql:variable("@oldValue"))]/@caption)[1]','VARCHAR(30)');
    SET @caption = REPLACE(@caption, @oldValue, @NewValue);

    SET @xml.modify('replace value of 
        (/Query/Columns/Column[fn:contains(@caption, sql:variable("@oldValue"))]/@caption)[1] with (sql:variable("@caption"))');
END

-- after
SELECT @xml;

Upvotes: 0

Related Questions