Manage XML data in SQL column when column is not of type XML

Below is a way to manage XML in SQL when the column is not of type XML

First, cast the data into a temp table as XML
DECLARE @results TABLE (Id INT, Detail XML)
--Filter results
INSERT INTO @results
select t.Id, d.extrainfo
from tableOne t
inner join into i on i.Id=t.Id
where i.institutionid=6 and t.state < 270

Combine relevent data for easier manipulation
DECLARE @x XML;
SELECT @x = ( SELECT
  [Id],
  [Detail].query('
  for $a in /*:RootNode
  return ')
FROM @results AS results
FOR XML AUTO);


Simple select statement
SELECT Id = T.Item.value('../@Id', 'INT'),
  Date = T.Item.value('@date', 'DATETIME')
FROM @x.nodes('results/detail') as T(Item)

Save DataSet as XML and then Recreate the DataSet from saved XML

In this example, the DataSet is also prepended with a distinct node that can be used to include specific data the DataSet didn't include.

using(DataSet ds = GetSpecificData(dataID)){
  XmlDocument xd = new XmlDocument();
  //Here is where the unique node is created and added to the XmlDocument
  XmlNode node = xd.CreateNode(XmlNodeType.Element, "NewRootOfXML", null);
  xd.AppendChild(node);

  using(MemoryStream ms = new MemoryStream()) {
    ds.WriteXml(ms);

    //Reset the position to the start of the stream
    ms.Seek(0, SeekOrigin.Begin);

    StreamReader sr = new StreamReader(ms);
    node.InnerXml = sr.ReadToEnd();
  }

  xd.SaveTheXML();
}

  XmlDocument xDoc = TheSavedXML();
  XmlNode nd = xDoc .SelectSingleNode("/NewRootOfXML");

  DataSet data = new DataSet();
  XmlTextReader reader = new XmlTextReader(nd.InnerXml, XmlNodeType.Element, null);
  data.ReadXml(reader);

SQL paging or selecting a specific range of rows

With a large set of data within a table, it is time consuming to query the entire set. To quickly return a specific section, follow the example below.

SELECT *
FROM
  (SELECT row_number() over (order by first, last DESC) as row, i.first, i.last, i.ex
    FROM information i
    WHERE i.infotype = @type
  ) AS Results
WHERE row BETWEEN 19000 AND 19900