XML data SELECT Syntax

Question

XML data SELECT Syntax

andyewx

Old Hand

Points: 347
More actions
June 12, 2023 at 6:52 am

#4209338
I'm still coming to grips with XML syntax.
Is there a more direct way of writing this SELECT?
This query as written works but looking through examples I feel like I have an extra select namely
```
Select inserted.lo_location FROM
```
If I'm not wrong that's good just looking to improve if I can.
```
SELECT * 
FROM
TEST.SDE.[label] 
Where TEST.SDE.[label].plot IN
   (Select inserted.lo_location FROM
      (SELECT
       X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
       FROM @inserted.nodes('inserted/row') AS T(X)
      ) AS Inserted)
```
regards
Andrew
June 28, 2023 at 9:31 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

Does this work? Likely not, as XML things tend to behave differently from how you would expect.

SELECT *
FROM TEST.SDE.label l
WHERE EXISTS
(
    SELECT 1
    FROM @inserted.nodes('inserted/row') T(X)
    WHERE T.X.query ('.').value ('(row/lo_Location)[1]', 'int') = l.plot
);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

DROP TABLE IF EXISTS #label ;
-- Create the table
CREATE TABLE #label 
(
    plot int,
)

-- Populate the table with sample data
INSERT INTO #label(plot)
VALUES (1), (2), (3), (4), (5)

-- Declare the XML variable with sample data
DECLARE @inserted XML = '
<inserted>
    <row>
        <lo_Location>2</lo_Location>
    </row>
    <row>
        <lo_Location>4</lo_Location>
    </row>
</inserted>'

-- Run the query
SELECT *
  FROM #label l
 WHERE EXISTS (SELECT 1
                 FROM @inserted.nodes('inserted/row') AS T(X)
                WHERE X.value('(lo_Location)[1]', 'int') = l.plot)
;