TSQL and Regular expression

Another excellent blog by Robyn and Phil:

http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/

Part 1: Using XML in SQL Server 2005

Suppose you want to generate following output from stored procedure :

1 <Companies>

2 <Company id="1" name="Company A">

3 <Address>

4 <Street>101 A St.Street>

5 <City>HaysCity>

6 <State>KansasState>

7 <Zip>67601Zip>

8 Address>

9 <Contacts>

10 <Contact id="1" name="Bob Black">

11 <ContactInfo email="bb@companyA.com" phone="(123) 456-7890" />

12 <Login uid="bb" pwd="bb7" />

13 Contact>

14 <Contact id="2" name="Bob Brown">

15 <ContactInfo email="bbn@companyA.com" phone="(123) 456-7891" />

16 <Login uid="bn" pwd="bn1" />

17 Contact>

18 <Contact id="3" name="Bob White">

19 <ContactInfo email="bw@companyA.com" phone="(123) 456-7892" />

20 <Login uid="bw" pwd="bw2" />

21 Contact>

22 Contacts>

23 Company>

24 Companies>

Following is the code snippet to achieve above output from SQL query

select

Company.Cmp_id "@id",

Company.[Name] "@name",

Company.Address "Address/Street",

Company.City "Address/City",

Company.State "Address/State",

Company.Zip "Address/Zip",

(

select

Contact.Cnt_id "@id",

Contact.[Name] "@name",

Contact.Email "ContactInfo/@email",

Contact.Phone "ContactInfo/@phone",

Contact.UserName "Login/@uid",

Contact.Password "Login/@pwd"

from

Contact

where

Contact.Cmp_id = @CompanyId

order by

Contact.[Name]

for xml path ('Contact'), root('Contacts'), type

)

from

Company

where

Company.Cmp_id = @CompanyId

for xml path ('Company'), root('Companies'), type

Showing enum as dropdown or listbox

Following function returns ListItemCollection for given enum type. This ListItemCollection can be bound to dropdown or listbox:

public static System.Web.UI.WebControls.ListItemCollection GetListItemsFromEnum(Type typeOfEnum)
{
System.Web.UI.WebControls.ListItemCollection lstColl
= new System.Web.UI.WebControls.ListItemCollection();
foreach (string name in Enum.GetNames(typeOfEnum))
{
System.Web.UI.WebControls.ListItem li
= new System.Web.UI.WebControls.ListItem();
li.Text = name;
li.Value = ((int)Enum.Parse(typeOfEnum, name)).ToString();
lstColl.Add(li);
}
return lstColl;
}