May 18, 2010 by Christoff Truter SQL XML
While playing around with the xml data type in SQL I noticed an interesting issue (maybe it will save someone some time), observe the following snippet:
DECLARE @cities AS XML SET @cities = '<cities> <city cityID="1" value="Berlyn" countryID="1" /> <city cityID="2" value="Hamburg" countryID="1" /> <city cityID="3" value="Frankfurt" countryID="1" /> <city cityID="4" value="Rotterdam" countryID="2" /> <city cityID="5" value="Amsterdam" countryID="2" /> </cities>' SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = 1]') t (item)
SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city') t (item) WHERE item.value('@countryID', 'INT') = @countryID
SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = ' + @countryID + ']') t (item)
DECLARE @countryID AS INT SET @countryID = 1 SELECT item.value('@cityID', 'INT') cityID, item.value('@value', 'VARCHAR(255)') value FROM @cities.nodes('//city[@countryID = sql:variable("@countryID")]') t (item)
October 19, 2010 by Kai Osmon
Thanks a lot for the statement: "This will however generate an error "The argument 1 of the xml data type method "nodes" must be a string literal", for this purpose e.g. interaction between relational and xml data, one can use expressions like sql:variable & sql:column." I've asked all of my colleagues but none gave me the answer. You did it tho... )