0

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything.

Here is my query:

DECLARE @ATID AS VARCHAR(3) = 12
DECLARE @AssetID AS VARCHAR(7) = 306

SELECT 
    tblXDataA.AssetID AS 'LID',
    tblA.AssetID AS 'AID',
    tblEM.[Custom] AS 'ABC/S XML',
    tblEMXData.[Custom] AS 'XData XML',
    tblEM.[Custom].value('(/ABCS/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> ABCS',
    tblEM.[Custom].value('(/ABC/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> ABC',
    tblEMXData.[Custom].value('(/XData/ABC/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> XData'
FROM 
    Asset AS tblA 
JOIN 
    LinkStr AS tblL 
        ON tblL.A_AssetID = tblA.AssetID 
JOIN 
    Asset AS tblXDataA 
        ON tblXDataA.AssetID = tblL.B_AssetID 
JOIN
    EMd AS tblEMXData
        ON tblEMXData.AssetID = tblXDataA.AssetID
JOIN 
    EMd AS tblEM 
        ON tblEM.AssetID = tblA.AssetID 
JOIN 
    LinkStr AS tbLinkStrB 
        ON tblXDataA.AssetID = tbLinkStrB.B_AssetID 
WHERE 
    tblXDataA.ATID = @ATID 
AND 
    tblA.AssetID = @AssetID;

All of that above produces a few columns of output but the main columns are the ABCS and XData:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

The above is correct as-is. There are 2 users and therefore the ABCS has a comma delimiter with both users ABCS. The first user that is added is the main user that all other users go under and therefore it has the same userID for both [306].

The users ABCS are:

User 1 [main] : Y40GB4DL4-48302343420105-FH3J-8798-11240

User 2 [addition]: 05649880-ED43

However, If I delete the last user added [05649880-ED43] then ABCS should only have 1 users ABCS in it instead of 2. But this is how its turning out:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

So depending on how many users are added to the main user is what I need to check for to make sure those users who get deleted are not left in the ABCS comma string.

Needing to:

Get each ABCS comma delimited value and compare it to XData(s)

So as the example above it would flow like this taken that the database looks like this:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

Is Y40GB4DL4-48302343420105-FH3J-8798-11240 [ABCS] present in [XData]? answer: true

Is 05649880-ED43 [ABCS] present in [XData]? answer: true

Since there are 2 users and 2 rows then that would, of course, return both as TRUE and that would be all.

Now If the added user gets deleted from the main user. The table would look like this and which is what currently is happening:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

Is Y40GB4DL4-48302343420105-FH3J-8798-11240 [ABCS] present in [XData]? answer: true

Is 05649880-ED43 [ABCS] present in [XData]? answer: false

Since its true and false then the false users ABCS ID needs to be taken out [DELETED/REMOVED] of the comma separated string. This is what I am unable to find out how to go about doing.

Help would be great!

6
  • I'd strongly recommend providing sample data as insert statements and table DDL in the question to make it easier for people to help you. May 12 at 21:46
  • Go to dbfiddle, construct a table a few sample records and your desired input. Put the material from the fiddle back here also...
    – Vérace
    May 13 at 5:24
  • Go to dbfiddle.uk, construct a table a few sample records and your desired output. Put the material from the fiddle back here also... help us to help you!
    – Vérace
    May 13 at 8:50
  • Why do you need to persist a CSV list (or an XML containing the CSV, for that matter) at a point when the underlying data can still change and thus affect that list? Or why do you want to solve this by modifying the list rather then re-generating the XML based on the changed conditions/circumstances/data?
    – Andriy M
    May 13 at 9:24
  • @AndriyM Going forward we will implement something that fixes the input to the XML so this isn't an issue any longer. However we have a LOT of records already that are messed up like this and looking for a way of correcting them without having to do it manually.
    – StealthRT
    May 13 at 12:46
0

You need to split the comma-delimited ABCS data into separate rows, then do a full join (if you are looking for ACBS has too many or too few entries) and look for NULL values. You could do this in-line with your existing query, but it gets messy, so here is how it might look if you dumped your results into a temp table.

Note: I used different AID numbers and some fabricated data for different "test cases"

DROP TABLE IF EXISTS #Data
CREATE  TABLE #Data(LID INT, AID INT, ABCS NVARCHAR(MAX), XDATA NVARCHAR(MAX))



INSERT INTO #Data VALUES
(481 ,306 ,'Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43','Y40GB4DL4-48302343420105-FH3J-8798-11240')
,(501 ,306 ,'Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43','05649880-ED43')
,(1481 ,1306 ,'140GB4DL4-48302343420105-FH3J-8798-11240,15649880-ED43','140GB4DL4-48302343420105-FH3J-8798-11240')
,(2481 ,2306 ,'240GB4DL4-48302343420105-FH3J-8798-11240,25649880-ED43','240GB4DL4-48302343420105-FH3J-8798-11240')
,(2501 ,2306 ,'240GB4DL4-48302343420105-FH3J-8798-11240,25649880-ED43','25649880-ED43')
,(1138 ,2306 ,'240GB4DL4-48302343420105-FH3J-8798-11240,25649880-ED43','8675309-C3P0')


SELECT DISTINCT COALESCE(split_acbs.aid, D.aid) AID, c.i.value('.', 'NVARCHAR(MAX)') acbs , D.xdata
FROM
(SELECT AID, TRY_CAST( '<i>' + REPLACE(ABCS, ',', '</i><i>') + '</i>' AS XML) codes FROM #DATA) split_acbs
    CROSS APPLY codes.nodes('//i') c(i)
FULL JOIN #data  d ON split_acbs.aid = D.aid AND c.i.value('.', 'NVARCHAR(MAX)')  = D.xdata
WHERE split_acbs.aid IS NULL OR D.aid IS NULL

Here is what the results look like:

AID         acbs              xdata
----------- ----------------- ------------------
1306        15649880-ED43     NULL
2306        NULL              8675309-C3P0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.