The right XQuery for inserting XML Attributes in XML variable in SQL Server based on if statement

I have the following xml:

<tr> <td>Lycamobile</td> <td>EV</td> <td>5.00</td> <td>0</td> <td>0</td> <td>13951</td> <td>42</td> <td>332</td> <td>13951</td> <td>0</td> <td>0</td> <td>332</td> </tr> <tr> <td>Lycamobile</td> <td>EV</td> <td>10.00</td> <td>0</td> <td>0</td> <td>6494</td> <td>0</td> <td>0</td> <td>6494</td> <td>0</td> <td>0</td> <td>0</td> </tr> <tr> <td>Lycamobile</td> <td>EV</td> <td>15.00</td> <td>0</td> <td>0</td> <td>7709</td> <td>0</td> <td>0</td> <td>7709</td> <td>0</td> <td>0</td> <td>0</td> </tr> <tr> <td>Lycamobile</td> <td>EV</td> <td>20.00</td> <td>0</td> <td>300</td> <td>8823</td> <td>0</td> <td>0</td> <td>8823</td> <td>0</td> <td>0</td> <td>0</td> </tr> <tr> <td>Orange</td> <td>ATC</td> <td>5.00</td> <td>0</td> <td>0</td> <td>10641</td> <td>182</td> <td>58</td> <td>10641</td> <td>0</td> <td>0</td> <td>58</td> </tr> <tr> <td>Orange</td> <td>ATC</td> <td>10.00</td> <td>0</td> <td>0</td> <td>6312</td> <td>12</td> <td>526</td> <td>6312</td> <td>0</td> <td>0</td> <td>526</td> </tr> <tr> <td>Orange</td> <td>ATC</td> <td>15.00</td> <td>0</td> <td>0</td> <td>7674</td> <td>0</td> <td>0</td> <td>7674</td> <td>0</td> <td>0</td> <td>0</td> </tr> <tr> <td>Orange</td> <td>ATC</td> <td>20.00</td> <td>0</td> <td>0</td> <td>8873</td> <td>0</td> <td>0</td> <td>8873</td> <td>0</td> <td>0</td> <td>0</td> </tr> <tr> <td>Vodafone</td> <td>EV</td> <td>5.00</td> <td>0</td> <td>0</td> <td>9911</td> <td>0</td> <td>0</td> <td>9911</td> <td>0</td> <td>0</td> <td>0</td> </tr>

I want to add a Attribute to every tr where the 12th td subelement is 58.

This would be the 5th tr in this example

The whole XML artifact is saved in variable @changesxml

I come so far:

SET @changesxml.modify('insert if (//tr/td[12]/text()="58") then attribute style {"background-color: #FF8B6F;"} else () as first into (/root/tr)[1] ')

But this adds it all the time to the first tr.

Grateful for any help.

-------------Problems Reply------------

It adds it all the time to the first tr because of


You're telling it to add it to the first tr element found under the root element.

Something like this will do it for the first tr found that has a 12th td with a value of 58:

SET @xml.modify('insert
attribute style {"background-color: #FF8B6F;"}
into (//tr[(td[12])/text()="58"])[1] ')

It'll only work for the first one found, but that should get you started. You'll need to create a loop to get all of them. If you create the loop and have the final indexer be dynamic then you will need to wrap everything into an sp_executesql and run it as a single command. If you try to do something like:

SET @xml.modify('insert
attribute style {"background-color: #FF8B6F;"}
into (//tr[(td[12])/text()="58"])[' + @val + '] ')

it will fail because .modify requires a string literal. So you'll need to do something like

declare @command nvarchar(1000)
declare @int int = 1

SET @command = '
SET @xml.modify(''insert
attribute style {"background-color: #FF8B6F;"}
into (//tr[(td[12])/text()="58"])[' + CAST(@int AS nvarchar(6)) + '] '') '

EXEC sp_executesql @stmt = @command,
@params = N'@xml xml out',
@xml = @xml OUTPUT


Where the variable @int is your counter variable.

Category:sql server Views:0 Time:2011-06-16

Related post

  • XML attribute vs XML element 2008-08-29

    At work we are being asked to create XML files to pass data to another offline application that will then create a second XML file to pass back in order to update some of our data. During the process we have been discussing with the team of the other

  • Xml attributes vs Xml inside properties? 2009-09-21

    Possible Duplicates: How to design storing complex object settings in an xml XML attribute vs XML element What's the criteria to use when deciding whether something should be done like this: <Blur Type="Gaussian", Amount=5></Blur> or <

  • How to insert rows in an Excel sheet into SQL Server 2005 or oracle 2010-05-30

    How to insert rows in an Excel sheet into SQL Server 2005 or oracle ? For example if I have 5000 rows in Excel sheet how can I insert these rows into a table in any database --------------Solutions------------- And for Oracle (see JYelton's answers f

  • Get SQL xml attribute value using variable 2012-02-10

    I have a SQL function that takes a variable called attribute, which is the xml attribute I want to get the value from. xmlPath is the full XML string. My xml looks like this: <EventSpecificData> <Keyword> <Word>myWord</Word>

  • Updating XML using attribute to identify value in SQL server 2010-06-17

    I have a XML structure in a XML column on a SQL Server table as follows: <Customizations> <Customization name="OtherValue"> <value>Test</value> </Customization> . . . . <Customization name="Year"> <value>2009

  • Transformation of XML tree to flat text using SQL Server 2008 XQuery 2011-04-20

    I have some XML data representing a mathematical expression tree and want to convert this to a flat math formula. Sounds simple, but the XQuery restrictions in SQL Server currently stopped me from succeeding (no recursive functions, problems with "he

  • Get XML from my SELECT on the SQL Server 2005 data 2010-11-11

    CREATE TABLE [dbo].[Project]( [ProjectId] [int] NOT NULL, [ProjectName] [nvarchar](255) , [ParentProjectId] [int] null, [ReleaseId] [int] ) insert into Project values (1, 'Project 1', null, 1) insert into Project values (2, 'Project 2', null, 1) inse

  • How to insert multiple records from to SQL Server 2005 table? 2010-05-06

    I have 40000 records in my DataTable. I want to insert all the records into SQL Server. I am using 2008 and SQL Server 2005. Currently I am using XML to pass from to SQL Server 2005 but it will give me network error. Is there any other

  • Query all XML files in a directory from SQL Server 2010-07-30

    Given a folder full of simple XML documents that all have the same structure, is there a quick way to either load all the documents into a temporary table or treat the folder as a table, in order to query the documents with the SQL 2005 XML query syn

  • How do we know how many columns come from XML variable in SQL Server 2005 2010-11-27

    I am passing XML type variable from one procedure to another procedure. Set XML variable like this Declare @XMLDOC XML set @XMLDOC = (select 60 as RecordDetailID, '' as ItemText, '' as ItemNote, 0 as DisplayOrder FOR XML RAW) In some case I need send

  • XML column in table with join SQL Server 2011-12-13

    I've got a table with an xml column called data (xml has got id node) and second table with idSecond column. I would like to perform join between these two tables so that data[id] = secondTable.ID What would be the syntax for this in SQL Server 2005

  • Insert blob (from mysql) data in varbinary (SQL Server) field with Zend_Db 2012-03-01

    I have a MySQL table that needs to be synchronized with a SQL Server table. So the data from MySQL moves to SQL Server. It is done by simple SELECT * and INSERT INTO queries. However, I ran into problems with migrating some BLOB data to a varbinary f

  • INSERTing data from a text file into SQL server (speed? method?) 2012-03-06

    Got about a 400 MB .txt file here that is delimited by '|'. Using a Windows Form with C#, I'm inserting each row of the .txt file into a table in my SQL server database. What I'm doing is simply this (shortened by "..." for brevity): while ((line = f

  • How do you insert a file (PDF) into a varbinary SQL Server column and later retrieve it? 2009-01-14

    I'm looking to take the results of a report run (a PDF file from Crystal Reports), serialize it, stick it into a varbinary field, and then later be able to deserialize it and present it back to the user. For now I have to just plain old ADO .NET (Sql

  • How much does wrapping inserts in a transaction help performance on Sql Server? 2009-02-06

    Ok so say I have 100 rows to insert and each row has about 150 columns (I know that sounds like a lot of columns, but I need to store this data in a single table). The inserts will occur at random, (ie whenever a set of users decide to upload a file

  • Inserting a dynamic number of rows into SQL Server 2009-02-19

    Is there a way to insert a dynamic number of rows from within sql server (.sql script) given the value of a look up, and setting one column for each insert? I want to attach a row with the foreign key of every row in a different table. For instance:

  • How to insert a blob into a database using sql server management studio 2009-10-29

    How can I easily insert a blob into a varbinary(MAX) field? for argument sake: assume the thing I want to insert is: c:\picture.png the table is mytable the column is mypictureblob and the place is recid=1 I've been googling for some time and I can't

  • xml Column update and Locking in Sql Server 2009-11-09

    I have a few windwos services. They get xml column from Sql server manipulate and update it. Service A- Gets XML Service B- Gets XML Service A- Updates XML (it will be lost) Service B- Updates XML I must lock row and I use next Code: SqlCommand cmdUp

  • How to insert exisitng documents stored on NFTS in sql server filestream's storage 2010-05-19

    I am doing investigation on filestream (asking on stackoverflow while reading whitepapers and google searching), in my current screnario documents are managed in this way: 1) I have a DB table where I keep the document id and the doc path (like \file

Copyright (C), All Rights Reserved.

processed in 0.066 (s). 11 q(s)