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

(/root/tr)[1]

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

SELECT @xml

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 Vb.net 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 vb.net 2008 and SQL Server 2005. Currently I am using XML to pass from vb.net 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) dskims.com, All Rights Reserved.

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