How to dynamically change the index value with SQL DML

Question

I am a beginner in XML with SQL. How can I specify index within an insert statement as below. The following statement gives throws exception with invalid path to update.

set @xmldata.modify('insert <Test>Test1</Test> after (/xyz/abc)[sql:variable("@index")]')

sql:variable() Function (XQuery) has some examples, but that doesn't speak about changing the index but other part of the statement.

Please post the actual code, table and some sample data. That will help ... — CRAFTY DBA, Jan 28 '14 at 19:03

score 1 · Answer 1 · answered Jan 28 '14 at 19:46

Did you check out this article. http://technet.microsoft.com/en-us/library/ms175466.aspx

SELECT @myDoc       
set @myDoc.modify('       
insert <BikeFrame>Strong long lasting</BikeFrame> 
after (/Root/ProductDescription/Features/Material)[1]       
')       
SELECT @myDoc;

This assumes that the Material tag exists. Is the path (/xyz/abc) valid?

<Root>
  <ProductDescription ProductID="1" ProductName="Road Bike">
    <Features>
      <Warranty>1 year parts and labor</Warranty>
      <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
      <Material>Aluminium</Material>
      <BikeFrame>Strong long lasting</BikeFrame>
    </Features>
  </ProductDescription>
</Root>

Also, test you current code with a literal instead of a sql:variable.

Again, please post the xml/code.

Thanks

John

Hey John, Thanks a lot for your reply. I did check the code without sql:variable which is working fine. Let's consider the example you put. `(/Root/ProductDescription/Features/Material)[1]` how do I replace `[1]` from this? — sarat, Jan 29 '14 at 05:56

score 1 · Answer 2 · answered Jan 29 '14 at 06:32

Expression2 in insert (XML DML) has to return a single node. The check is done when the query is parsed the first time and SQL Server can then not evaluate the predicate you are using to figure out if the expression will always return a single node. But if you specify a literal value in the brackets SQL Server knows that only one row will ever be returned.

To solve your issue you need to add a [1] to the end of your expression.

set @xmldata.modify('insert <Test>Test1</Test> 
                     after (/xyz/abc[sql:variable("@index")])[1]')

This will give you the node you want

/xyz/abc[sql:variable("@index")]

and this will tell SQL Server that the expression is guaranteed to return one node.

(/xyz/abc[sql:variable("@index")])[1]

SQL Fiddle

How to dynamically change the index value with SQL DML

2 Answers2

Linked