Get XML Data for mocking a Database Table

When creating a mock of a Database Table, it is helpful to have some pre-existing data rather than creating it all from scratch.
This can be accomplished by outputting the current content of a table to XML, then using this XML as the data source to test against.
To get hold of the data, in this example for the Project Table, for projects 1 to 5, one can use the following code

DECLARE @xmldata XML;
SET @xmldata = (
     SELECT *
     FROM [Project]
     WHERE ProjectId IN (1, 2, 3, 4, 5)
     FOR xml AUTO, ROOT ('ArrayOfProject'), ELEMENTS
);
SELECT @xmldata AS returnXml;

This will output the following data

<ArrayOfProject>
  <Project>
    <ProjectId>1</ProjectId>
    <ProjectName>First Project</ProjectName>
    <OrderNo>123456</OrderNo>
    ...
    <RowVer>AAAAAAA5MPA=</RowVer>
    <UpdatedByName>jonnus</UpdatedByName>
  </Project>
</ArrayOfProject>

Note that the table name in the XML must match the name of the class in the data layer for it t map properly. This exported XML data can then be imported into a Resources (resx) file with the key “Project”, and used as the seed to create a list of projects for use by your unit tests as follows

var stream = new MemoryStream(Encoding.UTF8.GetBytes(
    ResourceFileName.Project));
var reader = new StreamReader(stream);
var serializer = new XmlSerializer(typeof(
    Collection<Project>));
IEnumerable<Project> projects = 
    (Collection<Project>)serializer.Deserialize(reader);

(Source code inspired by https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.