One way to read Key-Value pairs from Visual Studio 2005 XML Resource Files into Table Columns
Localization is an important issue if your application is being used on different countries and is being used in different languages. Visual Studio offers great solutions for localization in .NET applications. Using the Resource files or creating Resource assemblies in order to use within the applications helps solving the localization challenge for .NET applications.
If you work on the resource files alot in order to update them you might experienced that this task is somehow difficult and a bit boring.
Unfotunately you may have to do such kinds of tasks during your daily jobs.
One common way of getting local translations of items and maintaining these data in resource files is sending the keys and descriptions in a file format to whom will use the application.
And translated data is requested from the key users of the application in the related country.
You may find it difficult to get the key-value pairs in the XML resource files. Microsoft SQL Server 2005 and its XML features and XML processing may help us in this situation.
Below you can find a simplified list of contents in a resource files from a Visual Studio .NET project.
declare @x xml
set @x =N'<root>
<!--
Microsoft ResX Schema
Version 2.0
The primary goals of this format is to allow a simple XML format
that is mostly human readable. The generation and parsing of the
various data types are done through the TypeConverter classes
associated with the data types.
Example:
... ado.net/XML headers & schema ...
<resheader name="resmimetype">text/microsoft-resx</resheader>
<resheader name="version">2.0</resheader>
<resheader name="reader">System.Resources.ResXResourceReader,
System.Windows.Forms, ...</resheader>
<resheader name="writer">System.Resources.ResXResourceWriter,
System.Windows.Forms, ...</resheader>
<data name="Name1"><value>this is my long string</value><comment>this is a
comment</comment></data>
<data name="Color1" type="System.Drawing.Color, System.Drawing">Blue</data>
<data name="Bitmap1"
mimetype="application/x-microsoft.net.object.binary.base64">
<value>[base64 mime encoded serialized .NET Framework object]</value>
</data>
<data name="Icon1" type="System.Drawing.Icon, System.Drawing"
mimetype="application/x-microsoft.net.object.bytearray.base64">
<value>[base64 mime encoded string representing a byte array form of the .NET
Framework object]</value>
<comment>This is a comment</comment>
</data>
There are any number of "resheader" rows that contain simple
name/value pairs.
Each data row contains a name, and value. The row also contains a
type or mimetype. Type corresponds to a .NET class that support
text/value conversion through the TypeConverter architecture.
Classes that don''t support this are serialized and stored with the
mimetype set.
The mimetype is used for serialized objects, and tells the
ResXResourceReader how to depersist the object. This is currently not
extensible. For a given mimetype the value must be set accordingly:
Note - application/x-microsoft.net.object.binary.base64 is the format
that the ResXResourceWriter will generate, however the reader can
read any of the formats listed below.
mimetype: application/x-microsoft.net.object.binary.base64
value : The object must be serialized with
: System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
: and then encoded with base64 encoding.
mimetype: application/x-microsoft.net.object.soap.base64
value : The object must be serialized with
: System.Runtime.Serialization.Formatters.Soap.SoapFormatter
: and then encoded with base64 encoding.
mimetype: application/x-microsoft.net.object.bytearray.base64
value : The object must be serialized into a byte array
: using a System.ComponentModel.TypeConverter
: and then encoded with base64 encoding.
-->
<xsd:schema id="root" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:import namespace="http://www.w3.org/XML/1998/namespace" />
<xsd:element name="root" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="metadata">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string" minOccurs="0" />
</xsd:sequence>
<xsd:attribute name="name" use="required" type="xsd:string" />
<xsd:attribute name="type" type="xsd:string" />
<xsd:attribute name="mimetype" type="xsd:string" />
<xsd:attribute ref="xml:space" />
</xsd:complexType>
</xsd:element>
<xsd:element name="assembly">
<xsd:complexType>
<xsd:attribute name="alias" type="xsd:string" />
<xsd:attribute name="name" type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="data">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1"
/>
<xsd:element name="comment" type="xsd:string" minOccurs="0"
msdata:Ordinal="2" />
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required"
msdata:Ordinal="1" />
<xsd:attribute name="type" type="xsd:string" msdata:Ordinal="3" />
<xsd:attribute name="mimetype" type="xsd:string" msdata:Ordinal="4" />
<xsd:attribute ref="xml:space" />
</xsd:complexType>
</xsd:element>
<xsd:element name="resheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1"
/>
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<resheader name="resmimetype">
<value>text/microsoft-resx</value>
</resheader>
<resheader name="version">
<value>2.0</value>
</resheader>
<resheader name="reader">
<value>System.Resources.ResXResourceReader, System.Windows.Forms,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
</resheader>
<resheader name="writer">
<value>System.Resources.ResXResourceWriter, System.Windows.Forms,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
</resheader>
<data name="ApplianceInfoNotFound" xml:space="preserve">
<value>The Appliance Code is not entered or is wrong, please control it
!</value>
</data>
<data name="AssignAUserToAllBusinessFields" xml:space="preserve">
<value>Please assign a user to all business fields</value>
</data>
<data name="AvailableInFuture" xml:space="preserve">
<value>This tab will be available in the future !</value>
</data>
<data name="BeginningDateGreaterEndingDate" xml:space="preserve">
<value>{0} must be less than {1}, please check it.</value>
</data>
<data name="CanNotSaveDueToUsercodeExists" xml:space="preserve">
<value>The record is not saved since the usercode is in use.</value>
</data>
<data name="CantUploadThisFileType" xml:space="preserve">
<value>The uploaded link is not available, please check the address.</value>
</data>
<data name="ConfirmAction" xml:space="preserve">
<value>Are you sure you want to perform this action ?</value>
</data>
<data name="ConfirmCancel" xml:space="preserve">
<value>Are you sure you want to cancel the list ?</value>
</data>
<data name="ConfirmDeleteParametericRecord" xml:space="preserve">
<value>Do you want to delete the record?</value>
</data>
<data name="ErrorOccuredWhenSaving" xml:space="preserve">
<value>An error has occurred!</value>
</data>
<data name="ErrorOccuredWhileExecuting" xml:space="preserve">
<value>An error has occured while executing. Please inform your system
administrator.</value>
</data>
<data name="FileNotSaved" xml:space="preserve">
<value>File is not saved !</value>
</data>
<data name="FileSizeTooLarge" xml:space="preserve">
<value>Size of the file you have attached is too large !</value>
</data>
<data name="InsertFailed" xml:space="preserve">
<value>Insert operation failed!</value>
</data>
<data name="PrmField1MustBeEqualToValue" xml:space="preserve">
<value>{0} must be equal to {1} !</value>
</data>
</root>'
And below you can find the XML query which can be used to fetch the localization item name or key and its value as two columns and one row per localization item.
select
ref.value('@name[1]',
'nvarchar(max)')
ResourceKey,
ref.value('./value[1]/.',
'nvarchar(max)')
Translation
from @x.nodes('/root/data')
as T(ref)
The result of the above XML query which is executed on a SQL Server 2005 or 2008 client is shown in the below screenshot.
You see, as a sql developer you have implemented a simple way for xml data conversion. And we have stored localization xml data in sql server database tables. This method we have seen as a sample solution, is a way of xml to database which can be implemented in MS SQL Server 2005 and Microsoft SQL Server 2008 which support xml natively. You can convert xml data into any other schema or structure you want by using enhanced XML support in Microsoft SQL Server 2005 like XML data type, XML data type methods, XQuery, etc. Also it is easy to get xml data from database to xml.