looking for value in xml string

• tcronin 95651

  SSCertifiable

  Points: 6735

  More actions

  May 10, 2017 at 1:49 pm

  #325577

  I have a table defined as 
  id int
  ordertxt varchar(3000)

  in the table called INQ you will have values like

  1,<SiteID>JCF</SiteID><entryid>4333</entryid>
  2,<SiteID>JCF</SiteID><entryid>6333</entryid>
  1,<SiteID>JCF</SiteID><entryid>4333</entryid>

  Developer had bad code need to remove the duplicate records that have same entryid, would substring be better or xml parse

• DesNorton

  SSC-Insane

  Points: 24561

  More actions

  May 10, 2017 at 2:37 pm

  #1942096

  I would parse the xml, like this

  
declare @INQ table (
id int
, ordertxt varchar(3000)
)

  insert into @INQ (id, ordertxt)
values
  ( 1, '<SiteID>JCF</SiteID><entryid>4333</entryid>' )
, ( 2, '<SiteID>JCF</SiteID><entryid>6333</entryid>' )
, ( 1, '<SiteID>JCF</SiteID><entryid>4333</entryid>' );

  with cteINQ AS (
select
  id
  , ordertxt = cast(ordertxt as xml)
from @INQ
)
, cteShred as (
select
  INQ.id
, entryid = I.o.value('text()[1]', 'INT')
, rn = ROW_NUMBER() over (partition by I.o.value('text()[1]', 'INT')
          order by id)
from cteINQ AS INQ
cross apply INQ.ordertxt.nodes('entryid') as I(o)
)
delete cteShred
where rn > 1;

  select *
from @INQ;


  ---

  How to post data/code on a forum to get the best help.