Using flowr to populate a table

• ducon

  Hall of Fame

  Points: 3801

  More actions

  August 27, 2009 at 7:31 pm

  #99836

  Hi there,

  I'm not sure whether this should go in T-SQL or XML but I figured I'd try my luck here first 🙂

  Everything I'm referring to here is done in T-SQL inside a stored procedure.

  I have an xml variable in T-SQL that contains a root node with a sequence. What I'm trying to achieve is to have my sequence be converted into a set of rows that go into a table. I was thinking of using flowr for that but I'm having issues since the .query() method only returns a singleton.

  Here's the code I have so far, I'm just looking at pointers on how I can achieve this, not necessarily a solution (although that would be nice ;)). And if on the way I can understand better why I'm not managing to get the result I want, it'd be fantastic.

  IF object_id('#aTemp') > 0

  DROP TABLE #aTemp;

  go

  DECLARE @aXMLDoc xml;

  SELECT @aXMLDoc = ''

  PRINT CONVERT(nvarchar(max), @aXMLDoc);

  SELECT @aXMLDoc.value('data((//T1/@value)[1])', 'varchar(max)')

  CREATE TABLE #aTemp(avalue xml not null);

  INSERT INTO #aTemp (avalue)

  SELECT @aXMLDoc.query('

  for $xml in //T1

  return $xml')

  SELECT * FROM #aTemp;

  DROP TABLE #aTemp;

  So basically, I want 2 rows in my temp table, one for value 1 and the other for value 2. I have a feeling the flowr may not be my best friend here because of the singleton returned by query, but would you guys know a better way?

  Many thanks in advance,

  Greg

  P.S: Right now, the table has an xml datatype, but ultimately, I want an int in there. I just put it that way because that's the closest I got to my goal 😉

• ducon

  Hall of Fame

  Points: 3801

  More actions

  August 30, 2009 at 7:12 pm

  #1046694

  Hi,

  Just thought I'd post an update. I could not get this to work using a flowr so what I did is that I used a count() in xquery and set a t-sql variable with that. Then in t-sql, I have a while loop that uses xqueries to retrieve the appropriate elements using the sql:variable("@i") 'function'.

  Regards,

  Greg