xml modify

declare @xml xml
set @xml=’<ContactInfo>
<LoginUserID>135</LoginUserID>
<Result>true</Result>
<MyLandLine1>
<MyPhone>
<Id>0</Id>
<Phone>AAA</Phone>
<ContactType>0</ContactType>
<PhoneType />
</MyPhone>
<MyPhone>
<Id>0</Id>
<Phone>BBB</Phone>
<ContactType>1</ContactType>
<PhoneType>Landline1</PhoneType>
</MyPhone>
</MyLandLine1>
</ContactInfo>’
 
 
SET @xml.modify(‘delete /ContactInfo/MyLandLine1/MyPhone[Phone[text() = "AAA"]]’)
select @xml

 

================

declare @xml xml

set @xml=’<ContactInfo>

<LoginUserID>135</LoginUserID>

<Result>true</Result>

<MyLandLine1>

<MyPhone>

<Id>0</Id>

<Phone>AAA</Phone>

<ContactType>0</ContactType>

<PhoneType />

</MyPhone>

<MyPhone>

<Id>0</Id>

<Phone>BBB</Phone>

<ContactType>1</ContactType>

<PhoneType>Landline1</PhoneType>

</MyPhone>

</MyLandLine1>

</ContactInfo>’

 

SET @xml.modify(‘insert <MyInformation>My Information</MyInformation> as first into (/ContactInfo)[1]‘)

select @xml

====================

SELECT 

select @XMLResult

 

@RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,‘varchar(10)’) set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)

 

 

condition in COUNT using NULLIF

SELECT g.hair, ( COUNT( * ) ) AS girl_count, ( COUNT( NULLIF( did_date, 0 ) ) ) AS did_date_count, ( COUNT( NULLIF( did_date, 1 ) ) ) AS did_not_date_count FROM @girl g GROUP BY g.hair