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