Thursday, July 5, 2018

SQL - How to drop table with foreign key constraints

In SQL Server the foreign key constraints must be dropped before dropping the table.

Please use the below script to find and drop all foreign key constraints of the table.


--Find and drop the constraints

DECLARE @dynamicSQL VARCHAR(MAX)
DECLARE MY_CURSOR CURSOR

LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT dynamicSQL = 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE object_name(referenced_object_id)  in ('table1', 'table2', 'table3')
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @dynamicSQL
EXEC (@dynamicSQL)

FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR


-- Drop tables
DROP 'table1'
DROP 'table2'
DROP 'table3'


Thursday, April 5, 2018

What are the special characters in XML?

How to handle special characters in XML?

In XML, five characters are reserved for internal use and you must replace them by replacement text when they are used in data.

The following table shows the characters that must be replaced by their replacement text in XML files

XML Special Characters

Reserved Character Replacement Text
" "
' '
> >
< &lt;
& &amp;

Friday, November 28, 2014

C#.Net Basic Design Patterns

.Net Basic Design Patterns:



Creational Patterns
Abstract Factory  Creates an instance of several families of classes
Builder  Separates object construction from its representation
Factory Method  Creates an instance of several derived classes
Prototype  A fully initialized instance to be copied or cloned
Singleton  A class of which only a single instance can exist
Structural Patterns
Adapter  Match interfaces of different classes
Bridge  Separates an object’s interface from its implementation
Composite  A tree structure of simple and composite objects
Decorator  Add responsibilities to objects dynamically
Facade  A single class that represents an entire subsystem
Flyweight  A fine-grained instance used for efficient sharing
Proxy  An object representing another object
Behavioral Patterns
Chain of Resp.  A way of passing a request between a chain of objects
Command  Encapsulate a command request as an object
Interpreter  A way to include language elements in a program
Iterator  Sequentially access the elements of a collection
Mediator  Defines simplified communication between classes
Memento  Capture and restore an object's internal state
Observer  A way of notifying change to a number of classes
State  Alter an object's behavior when its state changes
Strategy  Encapsulates an algorithm inside a class
Template Method  Defer the exact steps of an algorithm to a subclass
Visitor  Defines a new operation to a class without change

How to Convert XML string into SQL XML object

Convert XML string into SQL XML object



Create a proc in the Data base like..

create proc InsertXMLValue 
(@XMLString XML)
as
begin

-- Code to insert into the specific table which actually have the XML datatype column
end
And call this proc from the applicatio, use the SQL command object to call this proc and create a parameter like...
SqlParameter
 sqlParam = new SqlParameter("@XMLString"SqlDbType.Xml);
sqlParam.Value = strXML;
Pass the string value to the strXML variable.

How to sort a page or chapter index numbers stored in a nvarchar fileld?

SQL - How to sort a page or chapter index numbers stored in a varchar fileld


1.3.1
1.3.2
1.3.3
1.1.3
1.10
1.10.1
1.10.2
1.10.3
1.2
1.2.1
1.2.10
1.2.11


Here is the solution for this...




-- Sample table with page or chapeter index to sort
SELECT
'1.1.0' PageIndex INTO #MY_TESTUNION SELECT '1.3.1' UNION SELECT '1.3.2' UNION SELECT '1.3.3' UNION SELECT '1.1.3' UNION SELECT '1.10' UNION SELECT '1.10.1' UNION SELECT '1.10.2' UNION SELECT '1.10.3' UNION SELECT '1.2' UNION SELECT '3.10.1'UNION SELECT '3.9.1'UNION SELECT '11.1.0'

-- UD Function to sort the index numbers
create function dbo.SortNumber(@indexnum varchar(50))returns varchar(50)as
begin
declare @curlength int, @length intdeclare @rtnstring varchar(50)select @rtnstring = '', @curlength = 0while (@curlength < len(@indexnum)) -- loop until reach the last "." markbeginset @length = charindex('.', @indexnum, @curlength + 1)if @length = 0set @length = len(@indexnum) + 1-- pading with leading zeroset @rtnstring = @rtnstring + right('0000' + substring(@indexnum, @curlength + 1, @length - @curlength - 1), 4) + '.'set @curlength = @lengthendreturn @rtnstringend

--sample output of this function
SELECT dbo.SortNumber('1.10.1') = 0001.0010.0001.SELECT dbo.SortNumber('1.1.2') = 0001.0001.0002.  
-- final result
SELECT PageIndex,dbo.SortNumber(PageIndex) SortedIndex FROM #MY_TEST ORDER BY SortedIndex

SQL - Get Fiscal Year From Date


In SQL Reporting often we have the scenario where we need to do the reporting based on financial  year. Fiscal year may vary from one organization to another, so feel free to update the function, Currently it set to Australian Financial Year.

 SQL Function:

CREATE FUNCTION [dbo].[fnGetFiscalYear](
@CalenderDate DATETIME)
RETURNS NVARCHAR(10)
AS
BEGIN

DECLARE @FYYear INT
DECLARE @ResultVar NVARCHAR(10)

SET @ResultVar = ''

SELECT @FYYear = CASE WHEN MONTH(@CalenderDate) > 6 --New Fiscal Year stars on the fist day of July
THEN YEAR(@CalenderDate) + 1
ELSE YEAR(@CalenderDate)
END

SELECT @ResultVar  = 'FY-' + RIGHT(@FYYear,2)

RETURN @ResultVar
END


Example:

SELECT GETDATE(), DBO.fnGetFiscalYear(GETDATE())


Result:

2014-11-28 13:52:21.603 FY-15


Wednesday, March 5, 2014

How to Remove Special Characters Using SQL

Remove Special Characters Using TSQL


CREATE FUNCTION [dbo].[RemoveSpecialCharacters](@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

    Declare @KeepValues as varchar(50)

SET @StrValue = LTRIM(RTRIM(@StrValue))

    SET @KeepValues = '%[^a-z0-9]%'

    WHILE PATINDEX(@KeepValues, @StrValue) > 0
        SET @StrValue = STUFF(@StrValue, PATINDEX(@KeepValues, @StrValue), 1, '')

    Return @StrValue
END


Result:

SELECT DBO.RemoveSpecialCharacters('Welcome %To $Axil - 007')

WelcomeToAxil007

Remove / Trim Leading Zeros - SQL Server

TSQL - Removing leading zeros from varchar value:

How to remove leading zeros from a number using TSQL

ALTER FUNCTION [dbo].[RemoveLeadingZero] (@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
SET @StrValue = ISNULL(@StrValue,'')

SET @StrValue =  SUBSTRING(@StrValue, PATINDEX('%[^0]%', @StrValue+'.'), LEN(@StrValue))
   
Return @StrValue
END

Result:

SELECT DBO.RemoveLeadingZero('00050407')

50407

SELECT DBO.RemoveLeadingZero('00050470')

50470

SELECT DBO.RemoveLeadingZero('000.5430')

.5430

Monday, August 12, 2013

SQL - Get Date From Week Number & Year

Convert week number back into the date range for that week using T-SQL

DECLARE @Week INT
DECLARE @Year NVARCHAR(4);

SELECT @Year = 2014, @Week = 40

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 6, '1/1/' + @Year) + (@Week-1), 6) AS StartOfWeek;
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 5, '1/1/' + @Year) + (@Week-1), 5) AS EndOfWeek;

Wednesday, March 13, 2013

List of Country


<row CountryID="1" CountryName="Afghanistan" CountryCode="AFG" CountryCodeShort="AF" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="2" CountryName="Albania" CountryCode="ALB" CountryCodeShort="AL" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="3" CountryName="Algeria" CountryCode="DZA" CountryCodeShort="DZ" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="4" CountryName="American Samoa" CountryCode="ASM" CountryCodeShort="AS" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="5" CountryName="Andorra" CountryCode="AND" CountryCodeShort="AD" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="6" CountryName="Angola" CountryCode="AGO" CountryCodeShort="AO" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="7" CountryName="Anguilla" CountryCode="AIA" CountryCodeShort="AI" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="8" CountryName="Antarctica" CountryCode="ATA" CountryCodeShort="AQ" DisplayOrder="1" CountryRegion="Antarctica" />
<row CountryID="9" CountryName="Antigua and Barbuda" CountryCode="ATG" CountryCodeShort="AG" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="10" CountryName="Argentina" CountryCode="ARG" CountryCodeShort="AR" DisplayOrder="1" CountryRegion="Southern South America" />
<row CountryID="11" CountryName="Armenia" CountryCode="ARM" CountryCodeShort="AM" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="12" CountryName="Aruba" CountryCode="ABW" CountryCodeShort="AW" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="13" CountryName="Australia" CountryCode="AUS" CountryCodeShort="AU" DisplayOrder="0" CountryRegion="Australia" />
<row CountryID="14" CountryName="Austria" CountryCode="AUT" CountryCodeShort="AT" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="15" CountryName="Azerbaijan" CountryCode="AZE" CountryCodeShort="AZ" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="16" CountryName="Bahamas" CountryCode="BHS" CountryCodeShort="BS" DisplayOrder="1" CountryRegion="Caribbean" />
<row CountryID="17" CountryName="Bahrain" CountryCode="BHR" CountryCodeShort="BH" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="18" CountryName="Bangladesh" CountryCode="BGD" CountryCodeShort="BD" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="19" CountryName="Barbados" CountryCode="BRB" CountryCodeShort="BB" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="20" CountryName="Belarus" CountryCode="BLR" CountryCodeShort="BY" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="21" CountryName="Belgium" CountryCode="BEL" CountryCodeShort="BE" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="22" CountryName="Belize" CountryCode="BLZ" CountryCodeShort="BZ" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="23" CountryName="Benin" CountryCode="BEN" CountryCodeShort="BJ" DisplayOrder="1" CountryRegion="West Africa" />
<row CountryID="24" CountryName="Bermuda" CountryCode="BMU" CountryCodeShort="BM" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="25" CountryName="Bhutan" CountryCode="BTN" CountryCodeShort="BT" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="26" CountryName="Bolivia" CountryCode="BOL" CountryCodeShort="BO" DisplayOrder="1" CountryRegion="Central South America" />
<row CountryID="27" CountryName="Bosnia and Herzegowina" CountryCode="BIH" CountryCodeShort="BA" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="28" CountryName="Botswana" CountryCode="BWA" CountryCodeShort="BW" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="29" CountryName="Bouvet Island" CountryCode="BVT" CountryCodeShort="BV" DisplayOrder="1" CountryRegion="" />
<row CountryID="30" CountryName="Brazil" CountryCode="BRA" CountryCodeShort="BR" DisplayOrder="1" CountryRegion="Central Eastern South America" />
<row CountryID="31" CountryName="British Indian Ocean Territory" CountryCode="IOT" CountryCodeShort="IO" DisplayOrder="1" CountryRegion="" />
<row CountryID="32" CountryName="Brunei Darussalam" CountryCode="BRN" CountryCodeShort="BN" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="33" CountryName="Bulgaria" CountryCode="BGR" CountryCodeShort="BG" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="34" CountryName="Burkina Faso" CountryCode="BFA" CountryCodeShort="BF" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="35" CountryName="Burundi" CountryCode="BDI" CountryCodeShort="BI" DisplayOrder="1" CountryRegion="Eastern Africa, African Great Lakes" />
<row CountryID="36" CountryName="Cambodia" CountryCode="KHM" CountryCodeShort="KH" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="37" CountryName="Cameroon" CountryCode="CMR" CountryCodeShort="CM" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="38" CountryName="Canada" CountryCode="CAN" CountryCodeShort="CA" DisplayOrder="1" CountryRegion="North North America" />
<row CountryID="39" CountryName="Cape Verde" CountryCode="CPV" CountryCodeShort="CV" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="40" CountryName="Cayman Islands" CountryCode="CYM" CountryCodeShort="KY" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="41" CountryName="Central African Republic" CountryCode="CAF" CountryCodeShort="CF" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="42" CountryName="Chad" CountryCode="TCD" CountryCodeShort="TD" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="43" CountryName="Chile" CountryCode="CHL" CountryCodeShort="CL" DisplayOrder="1" CountryRegion="Western South America" />
<row CountryID="44" CountryName="China" CountryCode="CHN" CountryCodeShort="CN" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="45" CountryName="Christmas Island" CountryCode="CXR" CountryCodeShort="CX" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="46" CountryName="Cocos (Keeling) Islands" CountryCode="CCK" CountryCodeShort="CC" DisplayOrder="1" CountryRegion="South-East Asia, Australia" />
<row CountryID="47" CountryName="Colombia" CountryCode="COL" CountryCodeShort="CO" DisplayOrder="1" CountryRegion="North West South America" />
<row CountryID="48" CountryName="Comoros" CountryCode="COM" CountryCodeShort="KM" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="49" CountryName="Congo" CountryCode="COG" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="50" CountryName="Cook Islands" CountryCode="COK" CountryCodeShort="CK" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="51" CountryName="Costa Rica" CountryCode="CRI" CountryCodeShort="CR" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="52" CountryName="Cote D'Ivoire" CountryCode="CIV" CountryCodeShort="CI" DisplayOrder="1" CountryRegion="" />
<row CountryID="53" CountryName="Croatia (local Name: Hrvatska)" CountryCode="HRV" CountryCodeShort="HR" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="54" CountryName="Cuba" CountryCode="CUB" CountryCodeShort="CU" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="55" CountryName="Cyprus" CountryCode="CYP" CountryCodeShort="CY" DisplayOrder="1" CountryRegion="Mediterranean, Western Asia" />
<row CountryID="56" CountryName="Czech Republic" CountryCode="CZE" CountryCodeShort="CZ" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="57" CountryName="Denmark" CountryCode="DNK" CountryCodeShort="DK" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="58" CountryName="Djibouti" CountryCode="DJI" CountryCodeShort="DJ" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="59" CountryName="Dominica" CountryCode="DMA" CountryCodeShort="DM" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="60" CountryName="Dominican Republic" CountryCode="DOM" CountryCodeShort="DO" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="61" CountryName="Ecuador" CountryCode="ECU" CountryCodeShort="EC" DisplayOrder="1" CountryRegion="North West South America" />
<row CountryID="62" CountryName="Egypt" CountryCode="EGY" CountryCodeShort="EG" DisplayOrder="1" CountryRegion="Africa, Middle East" />
<row CountryID="63" CountryName="El Salvador" CountryCode="SLV" CountryCodeShort="SV" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="64" CountryName="Equatorial Guinea" CountryCode="GNQ" CountryCodeShort="GQ" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="65" CountryName="Eritrea" CountryCode="ERI" CountryCodeShort="ER" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="66" CountryName="Estonia" CountryCode="EST" CountryCodeShort="EE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="67" CountryName="Ethiopia" CountryCode="ETH" CountryCodeShort="ET" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="68" CountryName="Falkland Islands (Malvinas)" CountryCode="FLK" CountryCodeShort="FK" DisplayOrder="1" CountryRegion="Southern South America" />
<row CountryID="69" CountryName="Faroe Islands" CountryCode="FRO" CountryCodeShort="FO" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="70" CountryName="Fiji" CountryCode="FJI" CountryCodeShort="FJ" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="71" CountryName="Finland" CountryCode="FIN" CountryCodeShort="FI" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="72" CountryName="France" CountryCode="FRA" CountryCodeShort="FR" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="73" CountryName="French Guiana" CountryCode="GUF" CountryCodeShort="GF" DisplayOrder="1" CountryRegion="Northern South America" />
<row CountryID="74" CountryName="French Polynesia" CountryCode="PYF" CountryCodeShort="PF" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="75" CountryName="French Southern Territories" CountryCode="ATF" CountryCodeShort="TF" DisplayOrder="1" CountryRegion="Southern South America, Antarctic" />
<row CountryID="76" CountryName="Gabon" CountryCode="GAB" CountryCodeShort="GA" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="77" CountryName="Gambia" CountryCode="GMB" CountryCodeShort="GM" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="78" CountryName="Georgia" CountryCode="GEO" CountryCodeShort="GE" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="79" CountryName="Germany" CountryCode="DEU" CountryCodeShort="DE" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="80" CountryName="Ghana" CountryCode="GHA" CountryCodeShort="GH" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="81" CountryName="Gibraltar" CountryCode="GIB" CountryCodeShort="GI" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="82" CountryName="Greece" CountryCode="GRC" CountryCodeShort="GR" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="83" CountryName="Greenland" CountryCode="GRL" CountryCodeShort="GL" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="84" CountryName="Grenada" CountryCode="GRD" CountryCodeShort="GD" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="85" CountryName="Guadeloupe" CountryCode="GLP" CountryCodeShort="GP" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="86" CountryName="Guam" CountryCode="GUM" CountryCodeShort="GU" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="87" CountryName="Guatemala" CountryCode="GTM" CountryCodeShort="GT" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="88" CountryName="Guinea" CountryCode="GIN" CountryCodeShort="GN" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="89" CountryName="Guinea-bissau" CountryCode="GNB" CountryCodeShort="GW" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="90" CountryName="Guyana" CountryCode="GUY" CountryCodeShort="GY" DisplayOrder="1" CountryRegion="North Eastern South America" />
<row CountryID="91" CountryName="Haiti" CountryCode="HTI" CountryCodeShort="HT" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="92" CountryName="Heard and Mc Donald Islands" CountryCode="HMD" CountryCodeShort="HM" DisplayOrder="1" CountryRegion="" />
<row CountryID="93" CountryName="Honduras" CountryCode="HND" CountryCodeShort="HN" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="94" CountryName="Hong Kong" CountryCode="HKG" CountryCodeShort="HK" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="95" CountryName="Hungary" CountryCode="HUN" CountryCodeShort="HU" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="96" CountryName="Iceland" CountryCode="ISL" CountryCodeShort="IS" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="97" CountryName="India" CountryCode="IND" CountryCodeShort="IN" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="98" CountryName="Indonesia" CountryCode="IDN" CountryCodeShort="ID" DisplayOrder="1" CountryRegion="Maritime South-East Asia" />
<row CountryID="99" CountryName="Iran (Islamic Republic of)" CountryCode="IRN" CountryCodeShort="IR" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="100" CountryName="Iraq" CountryCode="IRQ" CountryCodeShort="IQ" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="101" CountryName="Ireland" CountryCode="IRL" CountryCodeShort="IE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="102" CountryName="Israel" CountryCode="ISR" CountryCodeShort="IL" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="103" CountryName="Italy" CountryCode="ITA" CountryCodeShort="IT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="104" CountryName="Jamaica" CountryCode="JAM" CountryCodeShort="JM" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="105" CountryName="Japan" CountryCode="JPN" CountryCodeShort="JP" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="106" CountryName="Jordan" CountryCode="JOR" CountryCodeShort="JO" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="107" CountryName="Kazakhstan" CountryCode="KAZ" CountryCodeShort="KZ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="108" CountryName="Kenya" CountryCode="KEN" CountryCodeShort="KE" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="109" CountryName="Kiribati" CountryCode="KIR" CountryCodeShort="KI" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="110" CountryName="Korea" CountryCode="KOR" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="111" CountryName="Korea, Democratic People's Republic of" CountryCode="PRK" CountryCodeShort="KP" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="112" CountryName="Kuwait" CountryCode="KWT" CountryCodeShort="KW" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="113" CountryName="Kyrgyzstan" CountryCode="KGZ" CountryCodeShort="KG" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="114" CountryName="Lao People's Democratic Republic" CountryCode="LAO" CountryCodeShort="LA" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="115" CountryName="Latvia" CountryCode="LVA" CountryCodeShort="LV" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="116" CountryName="Lebanon" CountryCode="LBN" CountryCodeShort="LB" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="117" CountryName="Lesotho" CountryCode="LSO" CountryCodeShort="LS" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="118" CountryName="Liberia" CountryCode="LBR" CountryCodeShort="LR" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="119" CountryName="Libyan Arab Jamahiriya" CountryCode="LBY" CountryCodeShort="LY" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="120" CountryName="Liechtenstein" CountryCode="LIE" CountryCodeShort="LI" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="121" CountryName="Lithuania" CountryCode="LTU" CountryCodeShort="LT" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="122" CountryName="Luxembourg" CountryCode="LUX" CountryCodeShort="LU" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="123" CountryName="Macau" CountryCode="MAC" CountryCodeShort="MO" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="124" CountryName="Macedonia" CountryCode="MKD" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="125" CountryName="Madagascar" CountryCode="MDG" CountryCodeShort="MG" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="126" CountryName="Malawi" CountryCode="MWI" CountryCodeShort="MW" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="127" CountryName="Malaysia" CountryCode="MYS" CountryCodeShort="MY" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="128" CountryName="Maldives" CountryCode="MDV" CountryCodeShort="MV" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="129" CountryName="Mali" CountryCode="MLI" CountryCodeShort="ML" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="130" CountryName="Malta" CountryCode="MLT" CountryCodeShort="MT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="131" CountryName="Marshall Islands" CountryCode="MHL" CountryCodeShort="MH" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="132" CountryName="Martinique" CountryCode="MTQ" CountryCodeShort="MQ" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="133" CountryName="Mauritania" CountryCode="MRT" CountryCodeShort="MR" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="134" CountryName="Mauritius" CountryCode="MUS" CountryCodeShort="MU" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="135" CountryName="Mayotte" CountryCode="MYT" CountryCodeShort="YT" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="136" CountryName="Mexico" CountryCode="MEX" CountryCodeShort="MX" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="137" CountryName="Micronesia" CountryCode="FSM" DisplayOrder="1" CountryRegion="" />
<row CountryID="138" CountryName="Moldova" CountryCode="MDA" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="139" CountryName="Monaco" CountryCode="MCO" CountryCodeShort="MC" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="140" CountryName="Mongolia" CountryCode="MNG" CountryCodeShort="MN" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="141" CountryName="Montserrat" CountryCode="MSR" CountryCodeShort="MS" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="142" CountryName="Morocco" CountryCode="MAR" CountryCodeShort="MA" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="143" CountryName="Mozambique" CountryCode="MOZ" CountryCodeShort="MZ" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="144" CountryName="Myanmar" CountryCode="MMR" CountryCodeShort="MM" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="145" CountryName="Namibia" CountryCode="NAM" CountryCodeShort="NA" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="146" CountryName="Nauru" CountryCode="NRU" CountryCodeShort="NR" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="147" CountryName="Nepal" CountryCode="NPL" CountryCodeShort="NP" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="148" CountryName="Netherlands" CountryCode="NLD" CountryCodeShort="NL" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="149" CountryName="Netherlands Antilles" CountryCode="ANT" CountryCodeShort="AN" DisplayOrder="1" CountryRegion="Caribbean" />
<row CountryID="150" CountryName="New Caledonia" CountryCode="NCL" CountryCodeShort="NC" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="151" CountryName="New Zealand" CountryCode="NZL" CountryCodeShort="NZ" DisplayOrder="1" CountryRegion="Oceania; Australia" />
<row CountryID="152" CountryName="Nicaragua" CountryCode="NIC" CountryCodeShort="NI" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="153" CountryName="Niger" CountryCode="NER" CountryCodeShort="NE" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="154" CountryName="Nigeria" CountryCode="NGA" CountryCodeShort="NG" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="155" CountryName="Niue" CountryCode="NIU" CountryCodeShort="NU" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="156" CountryName="Norfolk Island" CountryCode="NFK" CountryCodeShort="NF" DisplayOrder="1" CountryRegion="" />
<row CountryID="157" CountryName="Northern Mariana Islands" CountryCode="MNP" CountryCodeShort="MP" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="158" CountryName="Norway" CountryCode="NOR" CountryCodeShort="NO" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="159" CountryName="Oman" CountryCode="OMN" CountryCodeShort="OM" DisplayOrder="1" CountryRegion="Middle East" />
<row CountryID="160" CountryName="Pakistan" CountryCode="PAK" CountryCodeShort="PK" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="161" CountryName="Palau" CountryCode="PLW" CountryCodeShort="PW" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="162" CountryName="Panama" CountryCode="PAN" CountryCodeShort="PA" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="163" CountryName="Papua New Guinea" CountryCode="PNG" CountryCodeShort="PG" DisplayOrder="1" CountryRegion="Maritime Southeast Asia, Melanesia, Oceania" />
<row CountryID="164" CountryName="Paraguay" CountryCode="PRY" CountryCodeShort="PY" DisplayOrder="1" CountryRegion="Central South America" />
<row CountryID="165" CountryName="Peru" CountryCode="PER" CountryCodeShort="PE" DisplayOrder="1" CountryRegion="Western South America" />
<row CountryID="166" CountryName="Philippines" CountryCode="PHL" CountryCodeShort="PH" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="167" CountryName="Pitcairn" CountryCode="PCN" CountryCodeShort="PN" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="168" CountryName="Poland" CountryCode="POL" CountryCodeShort="PL" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="169" CountryName="Portugal" CountryCode="PRT" CountryCodeShort="PT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="170" CountryName="Puerto Rico" CountryCode="PRI" CountryCodeShort="PR" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="171" CountryName="Qatar" CountryCode="QAT" CountryCodeShort="QA" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="172" CountryName="Reunion" CountryCode="REU" CountryCodeShort="RE" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="173" CountryName="Romania" CountryCode="ROM" CountryCodeShort="RO" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="174" CountryName="Russia" CountryCode="RUS" DisplayOrder="1" CountryRegion="Eastern Europe - Northern Asia" />
<row CountryID="175" CountryName="Rwanda" CountryCode="RWA" CountryCodeShort="RW" DisplayOrder="1" CountryRegion="Eastern Africa, African Great Lakes" />
<row CountryID="176" CountryName="Saint Kitts and Nevis" CountryCode="KNA" CountryCodeShort="KN" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="177" CountryName="Saint Lucia" CountryCode="LCA" CountryCodeShort="LC" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="178" CountryName="Saint Vincent and the Grenadines" CountryCode="VCT" CountryCodeShort="VC" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="179" CountryName="Samoa" CountryCode="WSM" CountryCodeShort="WS" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="180" CountryName="San Marino" CountryCode="SMR" CountryCodeShort="SM" DisplayOrder="1" CountryRegion="Southern Europe within Italy" />
<row CountryID="181" CountryName="Sao Tome and Principe" CountryCode="STP" CountryCodeShort="ST" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="182" CountryName="Saudi Arabia" CountryCode="SAU" CountryCodeShort="SA" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="183" CountryName="Senegal" CountryCode="SEN" CountryCodeShort="SN" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="184" CountryName="Serbia" CountryCode="SRB" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="185" CountryName="Seychelles" CountryCode="SYC" CountryCodeShort="SC" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="186" CountryName="Sierra Leone" CountryCode="SLE" CountryCodeShort="SL" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="187" CountryName="Singapore" CountryCode="SGP" CountryCodeShort="SG" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="188" CountryName="Slovakia (Slovak Republic)" CountryCode="SVK" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="189" CountryName="Slovenia" CountryCode="SVN" CountryCodeShort="SI" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="190" CountryName="Solomon Islands" CountryCode="SLB" CountryCodeShort="SB" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="191" CountryName="Somalia" CountryCode="SOM" CountryCodeShort="SO" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="192" CountryName="South Africa" CountryCode="ZAF" CountryCodeShort="ZA" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="193" CountryName="South Georgia &amp; South Sandwich Islands" CountryCode="SGS" DisplayOrder="1" CountryRegion="" />
<row CountryID="194" CountryName="Spain" CountryCode="ESP" CountryCodeShort="ES" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="195" CountryName="Sri Lanka" CountryCode="LKA" CountryCodeShort="LK" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="196" CountryName="St. Helena" CountryCode="SHN" DisplayOrder="1" CountryRegion="" />
<row CountryID="197" CountryName="St. Pierre and Miquelon" CountryCode="SPM" DisplayOrder="1" CountryRegion="" />
<row CountryID="198" CountryName="Sudan" CountryCode="SDN" CountryCodeShort="SD" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="199" CountryName="Suriname" CountryCode="SUR" CountryCodeShort="SR" DisplayOrder="1" CountryRegion="North-Eastern South America" />
<row CountryID="200" CountryName="Svalbard and Jan Mayen Islands" CountryCode="SJM" CountryCodeShort="SJ" DisplayOrder="1" CountryRegion="" />
<row CountryID="201" CountryName="Swaziland" CountryCode="SWZ" CountryCodeShort="SZ" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="202" CountryName="Sweden" CountryCode="SWE" CountryCodeShort="SE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="203" CountryName="Switzerland" CountryCode="CHE" CountryCodeShort="CH" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="204" CountryName="Syrian Arab Republic" CountryCode="SYR" CountryCodeShort="SY" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="205" CountryName="Taiwan" CountryCode="TWN" CountryCodeShort="TW" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="206" CountryName="Tajikistan" CountryCode="TJK" CountryCodeShort="TJ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="207" CountryName="Tanzania" CountryCode="TZA" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="208" CountryName="Thailand" CountryCode="THA" CountryCodeShort="TH" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="209" CountryName="Togo" CountryCode="TGO" CountryCodeShort="TG" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="210" CountryName="Tokelau" CountryCode="TKL" CountryCodeShort="TK" DisplayOrder="1" CountryRegion="Oceania/Australia" />
<row CountryID="211" CountryName="Tonga" CountryCode="TON" CountryCodeShort="TO" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="212" CountryName="Trinidad and Tobago" CountryCode="TTO" CountryCodeShort="TT" DisplayOrder="1" CountryRegion="Northern South America, Caribbean" />
<row CountryID="213" CountryName="Tunisia" CountryCode="TUN" CountryCodeShort="TN" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="214" CountryName="Turkey" CountryCode="TUR" CountryCodeShort="TR" DisplayOrder="1" CountryRegion="Southeastern Europe, Western Asia" />
<row CountryID="215" CountryName="Turkmenistan" CountryCode="TKM" CountryCodeShort="TM" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="216" CountryName="Turks and Caicos Islands" CountryCode="TCA" CountryCodeShort="TC" DisplayOrder="1" CountryRegion="Caribbean, parts of the Bahamas island chain." />
<row CountryID="217" CountryName="Tuvalu" CountryCode="TUV" CountryCodeShort="TV" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="218" CountryName="Uganda" CountryCode="UGA" CountryCodeShort="UG" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="219" CountryName="Ukraine" CountryCode="UKR" CountryCodeShort="UA" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="220" CountryName="United Arab Emirates" CountryCode="ARE" CountryCodeShort="AE" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="221" CountryName="United Kingdom" CountryCode="GBR" CountryCodeShort="GB" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="222" CountryName="United States" CountryCode="USA" CountryCodeShort="US" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="223" CountryName="United States minor outlying islands" CountryCode="UMI" CountryCodeShort="UM" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="224" CountryName="Uruguay" CountryCode="URY" CountryCodeShort="UY" DisplayOrder="1" CountryRegion="Central East South America" />
<row CountryID="225" CountryName="Uzbekistan" CountryCode="UZB" CountryCodeShort="UZ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="226" CountryName="Vanuatu" CountryCode="VUT" CountryCodeShort="VU" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="227" CountryName="Vatican City State (Holy See)" CountryCode="VAT" CountryCodeShort="VA" DisplayOrder="1" CountryRegion="Southern Europe within Italy" />
<row CountryID="228" CountryName="Venezuela" CountryCode="VEN" CountryCodeShort="VE" DisplayOrder="1" CountryRegion="Northern South America" />
<row CountryID="229" CountryName="Viet Nam" CountryCode="VNM" CountryCodeShort="VN" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="230" CountryName="Virgin Islands (British)" CountryCode="VGB" CountryCodeShort="VG" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="231" CountryName="Virgin Islands (U.S.)" CountryCode="VIR" CountryCodeShort="VI" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="232" CountryName="Wallis and Futuna Islands" CountryCode="WLF" CountryCodeShort="WF" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="233" CountryName="Western Sahara" CountryCode="ESH" CountryCodeShort="EH" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="234" CountryName="Yemen" CountryCode="YEM" CountryCodeShort="YE" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="235" CountryName="Zambia" CountryCode="ZMB" CountryCodeShort="ZM" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="236" CountryName="Zimbabwe" CountryCode="ZWE" CountryCodeShort="ZW" DisplayOrder="1" CountryRegion="Eastern Africa" />