CREATE TABLE [dbo].[#tbl1]( [id] [int] IDENTITY(1,1) NOT ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

English

Q&A for linguists, etymologists, and serious English language enthusiasts

CREATE TABLE [dbo].[#tbl1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[myXml] [xml] NULL,
    )

 
insert into #tbl1(name,myXml)


select 'a','<Bands>
    <Band>
        <Name>beatles</Name>
        <Num>4</Num>
        <Score>5</Score>
    </Band>
    <Band>
        <Name>doors</Name>
        <Num>4</Num>
        <Score>3</Score>
    </Band>
    <Band>
        <Name>genesis</Name>
        <Num>3</Num>
        <Score>3</Score>
    </Band>
    <Band>
        <Name>bee gees</Name>
        <Num>3</Num>
        <Score>4</Score>
    </Band>
    <Band>
        <Name>simon and garfunkle</Name>
        <Num>2</Num>
        <Score>4</Score>
    </Band>
</Bands>'
 select * from #tbl1


   update #tbl1
set myXml.modify('replace value of (/Bands/Band[Score > 4]/Name/text())[1] 
                    with concat(string(sql:column("id")), "lalalala")')
where myXml.exist('/Bands/Band[Score > 4]') = 1
    
    select * from #tbl1

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results