Trying to query XML but getting null?!?Please advise

• stephenmbell

  SSC Eights!

  Points: 926

  More actions

  January 28, 2009 at 9:16 am

  #125957

  I am trying to work through the example @ http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

  ("Passing a Table To A Stored Procedure")

  So far, I believe I am able to get my data into an xml type variable. My problems is selecting from the xml variable to verify that the data is in there.

  This is what my code looks like: (I have removed the closing brackets on the xml so it would show on the page.)

  DECLARE @x XML

  SET @x = (SELECT vActiveGasSites.GasSiteID FROM vSitesInRegions INNER JOIN vActiveGasSites ON vSitesInRegions.GasSiteId = vActiveGasSites.GasSiteID INNER JOIN vCurrentInventoryAllSites A ON vActiveGasSites.GasSiteID = A.GasSiteID WHERE vSitesInRegions.RegionID = 1 AND A.PosProduct = 1 FOR XML RAW('GasSite'), ROOT('GasSites'), TYPE)

  /*

  XML LOOKS LIKE:

  <GasSites

  <GasSite GasSiteID="22" /

  <GasSite GasSiteID="61" /

  <GasSite GasSiteID="65" /

  <GasSite GasSiteID="78" /

  <GasSite GasSiteID="107" /

  </GasSites

  */

  -- verify that the data is in the variable

  SELECT x.GasSite.value('@GasSiteId[1]', 'INT') as GasSiteId

  FROM @x.nodes('//GasSites/GasSite') as x(GasSite)

  When I try to the verify query, I get the proper number of rows returned, but they all show NULL. If I do select @x I get the xml data shown above.

  Any help or direction would be greatly appreciated.

  Thank you.

  sb

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 28, 2009 at 9:27 am

  #933922

  @GasSiteId should be @GasSiteID (XML is case-sensitive)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537