x

Logic to modify multiple nodes of xml variable in sql server

Hello,

I am stuck working with an xml datatype in sql server.

I am trying to replace multiple nodes of an xml variable in sql server, but haven't been successful. Could you please share your thoughts on how I could handle the query?

Thank you...

Issue:

In the XML(variable), I am trying to set text in Value element to "White" for all attributes with CustomProperty Name="Color"

The Value element is like Black

But, Value can contain any other color, even "#rrggbb" RGB codes are allowed.

The xml:

 DECLARE @xmled XML
 SET @xmled =   
      <SemanticModel xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="Gd54bb96e-8704">
       <Culture>en-US</Culture>
       <Entities>
      <Entity ID="G6356ce91-8a88">
       <CustomProperties>
         <CustomProperty Name="UpdateRowKey">
           <Value xsi:type="xsd:string">Gee0eb52b-44ad</Value>
         </CustomProperty>
       </CustomProperties>
       <Name>Entity 1</Name>
       <CollectionName>Entity 1</CollectionName>
       <IdentifyingAttributes>
         <AttributeReference>
           <AttributeID>Gee0eb52b-308d-44ad-96e4-75b1cfa1d36e</AttributeID>
         </AttributeReference>
       </IdentifyingAttributes>
       <InstanceSelection>MandatoryFilter</InstanceSelection>
       <Fields>
         <Attribute ID="Gee0eb52b-308d">
           <CustomProperties>
             <CustomProperty Name="Color">
               <Value xsi:type="xsd:string">Red</Value>
             </CustomProperty>
           </CustomProperties>
           <Name>Attribute 1</Name>
           <DataType>String</DataType>
           <SortDirection>Ascending</SortDirection>
           <Width>4</Width>
           <DataCulture>en-US</DataCulture>
           <DiscourageGrouping>true</DiscourageGrouping>
           <EnableDrillthrough>true</EnableDrillthrough>
           <ContextualName>Merge</ContextualName>
           <Column Name="Attribute 1" />
         </Attribute>
         <Attribute ID="Gd93eaa72-a953">
           <CustomProperties>
             <CustomProperty Name="Color">
               <Value xsi:type="xsd:string">White</Value>
             </CustomProperty>
           </CustomProperties>
           <Name>Attribute 2</Name>
           <DataType>String</DataType>
           <Nullable>true</Nullable>
           <SortDirection>Ascending</SortDirection>
           <Width>2</Width>
           <DataCulture>en-US</DataCulture>
           <ValueSelection>Dropdown</ValueSelection>
           <Column Name="Attribute 2" />
         </Attribute>
         <Attribute ID="G834638ef-f136">
           <CustomProperties>
             <CustomProperty Name="Color">
               <Value xsi:type="xsd:string">Blue</Value>
             </CustomProperty>
           </CustomProperties>
           <Name>Attribute 3</Name>
           <DataType>String</DataType>
           <Nullable>true</Nullable>
           <SortDirection>Ascending</SortDirection>
           <Width>7</Width>
           <DataCulture>en-US</DataCulture>
           <ValueSelection>Dropdown</ValueSelection>
           <Column Name="Attribute 3" />
         </Attribute>
        </Fields>
         <Table Name="table1" />
        </Entity>
       </Entities>
     </SemanticModel>
 
more ▼

asked Aug 07, 2014 at 12:37 AM in Default

avatar image

aaryansh
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x438
x398
x202
x16

asked: Aug 07, 2014 at 12:37 AM

Seen: 702 times

Last Updated: Aug 07, 2014 at 01:06 AM

Copyright 2017 Redgate Software. Privacy Policy