For XML generic list deserialize

• James Appenzeller

  SSC Enthusiast

  Points: 149

  More actions

  December 12, 2007 at 6:01 pm

  #70532

  So far I've been using xml types and have been serializing/deserializing generic lists and honestly it's been great. Now I'm trying to take data not in XML columns and try to deserialize in a similar way. This may be confusing, but here goes.

  I have two tables. Table1 has an ID field and Table2 joins with the ID of Table1. Table2 is just a list of integers. So I may have something like

  Table1

  ID=12

  ID=13

  ...

  Table2

  Table1ID=12 MyInt=3

  Table1ID=12 MyInt=4

  Table1ID=13 MyInt=3

  ...

  What I would like to do is return 2 columns from a query. The first column would be the ID from Table1 and the second column would be an XML list. So far I've been using generics, so the XML of a generic list is something like this.

  <ArrayofInt><Int> 3</Int><Int> 4</Int></ArrayofInt>

  So the result I'm looking for from a query is as follows,

  column1 = 12 column2= <ArrayofInt><Int> 3</Int><Int> 4</Int></ArrayofInt>

  column1 = 13 column2= <ArrayofInt><Int> 3</Int></ArrayofInt>...

  So what I need is to mix columns where one column is an int and the other is an xml field.

  And the xml field comes from a join on another table.

  I hope this makes sense.

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 9, 2008 at 5:37 am

  #800276

  I think my timing may be a little off here, but anyway.

  This is pretty inelegant, but it is one way...

  create table Table1 (ID int)

  insert Table1

  select 12

  union all select 13

  union all select 14

  create table Table2 (ID int, MyInt int)

  insert Table2

  select 12, 3

  union all select 12, 4

  union all select 13, 3

  go

  CREATE FUNCTION MyIntXml(@Id int)

  RETURNS xml

  AS

  BEGIN

  declare @x xml

  set @x = (select MyInt "text()" from Table2 where Id = @Id FOR XML PATH ('Int'))

  set @x = '<ArrayofInt>' + isnull(cast(@x as varchar(100)), '') + '</ArrayofInt>'

  RETURN @x

  END;

  go

  select *, dbo.MyIntXml(Id) as MyIntXml from Table1

  /* results

  ID MyIntXml

  12 <ArrayofInt><Int>3</Int><Int>4</Int></ArrayofInt>

  13 <ArrayofInt><Int>3</Int></ArrayofInt>

  14 <ArrayofInt />

  */

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.