CODEWITHSUNDEEP
sql.netsql-server
Kumas
Kumas

Reputation: 45

In SQL Server XML column how do I verify whether same value is present in more than one rows or not

In a SQL Server XML column, how do I verify whether same value is present in more than one rows or not?

Value could be dynamic - it's not fixed. Kindly refer to the table shown below, where Id column is Primary Key and in ExtendedData column Tag value could be in more than 1 rows.

Query to INSERT record into table

DECLARE @Table1 TABLE (Id BIGINT, AccountNumber int,ExtendedData XML)

INSERT INTO @Table1 (Id,AccountNumber,ExtendedData) VALUES
(1, 14, '<ExtendedData> <Data>      <POLICYNBR>131313</POLICYNBR>      <ACCOUNTNBR>GGAHAHA</ACCOUNTNBR>      <ID>29499785613092400202</ID>  </Data></ExtendedData>'),
(2, 14, '<ExtendedData> <Data>      <POLICYNBR>131313</POLICYNBR>       <ACCOUNTNBR>GGAHAHA</ACCOUNTNBR>        <ID>29499785613092400202</ID>   </Data></ExtendedData>'),
(3, 14, '<ExtendedData> <Data>      <POLICYNBR>54555</POLICYNBR>        <ACCOUNTNBR>GGAHAHA</ACCOUNTNBR>        <ID>123485613092400202</ID>     </Data></ExtendedData>'),
(4, 13, '<ExtendedData> <Data>      <POLICYNBR>54555</POLICYNBR>        <ACCOUNTNBR>GGAHAHA</ACCOUNTNBR>        <ID>123485613092400202</ID> </Data></ExtendedData>')

Expected result :- If I pass Account id =14 then I should be able to see two records in the response, because both for the accountId=14 tag has same value in ExtendedData column.

This is my script : - But it seems to be its not working , it not giving the result which I am expecting.

select * from Table1 where AccountId =14 and 
ExtendedData.value('(/ExtendedData/Data/ID/text())[1]','varchar(10)') = select CAST(ExtendedData as XML).value('(/ExtendedData/Data/ID/)[1]', 'nvarchar(20)') from Table1 where ExtendedData.value('(/ExtendedData/Data/ID/text())[1]','varchar(10)')

Please suggest me

Table name is Table1, and Id column is the primary key. enter image description here

Upvotes: 0

Views: 96

Answers (2)

Charlieface
Charlieface

Reputation: 71579

Sounds like you just need a having, after having pulled out the relevant id value

select t.AccountId, v.ExtendedId
from Table1 t
cross apply (values(
    ExtendedData.value('(/ExtendedData/Data/ID/text())[1]','varchar(10)')
) ) v(ExtendedId)
where AccountId = 14
group by t.AccountId, v.ExtendedId
having count(*) > 1

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7918

First for some re-usable sample data:

DECLARE @xml XML =
'<ExtendedData>
  <Data>
    <PolNbr>123999</PolNbr>
    <ActNbr>ABCZZZ</ActNbr>
    <ID>555444</ID>
  </Data>
</ExtendedData>
<ExtendedData>
  <Data>
    <PolNbr>123999</PolNbr>
    <ActNbr>ABCXXX</ActNbr>
    <ID>555667</ID>
  </Data>
</ExtendedData>
<ExtendedData>
  <Data>
    <PolNbr>123111</PolNbr>
    <ActNbr>ABCZZZ</ActNbr>
    <ID>555667</ID>
  </Data>
</ExtendedData>
<ExtendedData>
  <Data>
    <PolNbr>123113</PolNbr>
    <ActNbr>ABCXXX</ActNbr>
    <ID>555666</ID>
  </Data>
</ExtendedData>'

SELECT
  ID     = n.X.value('(ID)[1]','varchar(100)'),
  PolNbr = n.X.value('(PolNbr)[1]','VARCHAR(100)'),
  ActNbr = n.X.value('(ActNbr)[1]','varchar(100)')
FROM        (VALUES(@xml)) AS f(X)
CROSS APPLY f.X.nodes('/ExtendedData/Data') AS n(X);

Note the output:

ID       PolNbr    ActNbr
-------- --------- ----------
555444   123999    ABCZZZ
555667   123999    ABCXXX
555667   123111    ABCZZZ
555666   123113    ABCXXX

Now we can adjust our query to identify any duplicate value:

--==== ID
SELECT      ID_Duplicates = nd.X
FROM        (VALUES(@xml)) AS f(X)
CROSS APPLY f.X.nodes('/ExtendedData/Data') AS n(X)
CROSS APPLY (VALUES(n.X.value('(ID)[1]','varchar(100)'))) AS nd(X)
GROUP BY    nd.X
HAVING      COUNT(*) > 1

--==== PolNbr
SELECT      PolNbr_Duplicates = nd.X
FROM        (VALUES(@xml)) AS f(X)
CROSS APPLY f.X.nodes('/ExtendedData/Data') AS n(X)
CROSS APPLY (VALUES(n.X.value('(PolNbr)[1]','varchar(100)'))) AS nd(X)
GROUP BY    nd.X
HAVING      COUNT(*) > 1;

--==== PolNbr
SELECT      ActNbr_Duplicates = nd.X
FROM        (VALUES(@xml)) AS f(X)
CROSS APPLY f.X.nodes('/ExtendedData/Data') AS n(X)
CROSS APPLY (VALUES(n.X.value('(ActNbr)[1]','varchar(100)'))) AS nd(X)
GROUP BY    nd.X
HAVING      COUNT(*) > 1;

Results:

ID_Duplicates
------------------------------------------
555667

PolNbr_Duplicates
------------------------------------------
123999

ActNbr_Duplicates
------------------------------------------
ABCXXX
ABCZZZ

Upvotes: 0

Related Questions