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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  June 12, 2023 at 7:52 am

  #4209354

  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
  );

  ---

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  June 28, 2023 at 9:46 am

  #4221446

  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)
  ;