Yukon has enhanced the SQL Server 2000 FOR XML function. If you want to create element based XML (yay!), FOR XML PATH allows you to specify column aliases that contain valid XPath expressions that will shape your XML output.

--XML FOR PATH

Select

ContactID as [@Contact_ID],

FirstName as [ContactName/First],

LastName as [ContactName/Last],

Phone as [ContactPhone/Phone1]

From Person.Contact For XML PATH

Produces output like:

<Customer>

  <CustomerID>1</CustomerID>

  <OrderDetail>

    <SalesOrderID>43860</SalesOrderID>

    <OrderDate>2001-08-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

    <SalesOrderID>44501</SalesOrderID>

    <OrderDate>2001-11-01T00:00:00</OrderDate>

  </OrderDetail>

</Customer>

<Customer>

  <CustomerID>2</CustomerID>

  <OrderDetail>

    <SalesOrderID>46976</SalesOrderID>

    <OrderDate>2002-08-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

    <SalesOrderID>49054</SalesOrderID>

    <OrderDate>2003-02-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

</Customer>

If you are familiar and comfortable with XPath, there are some additional features to XML PATH you may like. You can use the following XPath node test functions to further control the shape of your XML output:

·        node()

·        text()

·        data()

·        comment()

·        processing-instruction()

SQL Server | Yukon Book

Monday, January 17, 2005 1:31:13 PM (Eastern Standard Time, UTC-05:00)
Comments [6]  |  Trackback

The XML Data Type can, just like other data types, conform to Nullability, Defaults and Constraints. If you want to make the field required (NOT NULL) and provide a default value on your XML column, just specify like you would any other column.

CREATE TABLE OrdersXML

   (OrderDocID INT PRIMARY KEY,

   xOrders XML NOT NULL Default '</Orders>'

This insert will work because it is relying on the default:

Insert Into OrdersXML (OrderDocID, xOrders) Values (2, DEFAULT)

The following insert will work, even if we add non <Orders> in our table because we have not declared a constraint on the column:

Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')

SQL Server 2005 gives you the opportunity to add a constraint. An interesting feature of an XML column is that the CHECK constraint is based on the XML Infoset model using the methods of the XML column. You can use the exist method, which is specific to the XML type, as part of our CHECK constraint to force a particular type of XML element to exist.  In the following example we will drop and recreate the table from scratch (you can use ALTER TABLE as well) adding a default and CHECK CONSTRAINT.

Drop Table OrdersXML

--create the table with the XML Datatype

--using a default value

--also has the check constraints

CREATE TABLE OrdersXML

   (OrderDocID INT PRIMARY KEY,

   xOrders XML NOT NULL Default '<Orders>'

 CONSTRAINT xml_orderconstraint

  CHECK(xOrders.exist('/Orders')=1))

Now this example from before will fail.

Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')

When you attempt to insert data that will violate the xml_orderconstraint you get the following error message from SQL Server:

Msg 547, Level 16, State 0, Line 1

INSERT statement conflicted with CHECK constraint

'xml_orderconstraint'. The conflict occurred in database

'AdventureWorks', table 'OrdersXML', column 'xOrders'.

The statement has been terminated.

SQL Server | Yukon Book

Tuesday, January 11, 2005 10:25:06 AM (Eastern Standard Time, UTC-05:00)
Comments [23]  |  Trackback

The Mets made a spash as did the Jets this weekend, but marathon training is right on schedule, a 5 mile race was ran on Sunday with the usual results (Kathleen beating us). This is the first of 9 races to qualify for the 2006 New York City Marathon. Antarctica marathon is 47 days away...

Antarctica | Professional Sports

Monday, January 10, 2005 5:44:37 PM (Eastern Standard Time, UTC-05:00)
Comments [6]  |  Trackback

Since I speak at the SDC in the Netherlands so much (every year since 1997 I think?) I decided to open a cafe in the Netherlands. Here is my buddy Remi Caron after he discovered my cafe.

Speaking | Travel

Thursday, January 06, 2005 5:40:09 PM (Eastern Standard Time, UTC-05:00)
Comments [15]  |  Trackback

This is a photo from the USS Abraham Lincoln over the Indonesian island of Sumatra.

Non-Techie

Tuesday, January 04, 2005 3:47:10 PM (Eastern Standard Time, UTC-05:00)
Comments [12]  |  Trackback

When you want to view a web site that has a Quick Time video and you go to Apple.com to download the free Quick Time player for Windows 2000 or XP, you HAVE to install iTunes. What does iTunes have to do with QuickTime? So many move previews are done in QuickTime and Apple knows this. So they are using their dominance in one area to force something else on us. Somebody call the US Justice Department.

Anyway Apple owns the iPod market, but a years worth of iPod sales is equal to 2 weeks of Dell shipments…

Rants

Monday, January 03, 2005 4:42:21 PM (Eastern Standard Time, UTC-05:00)
Comments [16]  |  Trackback

Osama bin Laden formed an official alliance with the Iraqi terrorist Sunni Zarqawi yesterday and urged the minority Sunnis to protest the Jan 30^th election in Iraq.

Previously bin Laden (who is a Sunni Muslim) has not been a vocal enemy against the Shiites. Remember Saddam was a minority Sunni kept in power by force and repressed the majority Shiite population. (As well as the minority Kurds.) By forming an official alliance with Zarqawi, bin Laden’s now pitting himself against the Shiite majority. (Like the good Wahhabist he really is.)

So January 30^th there will be elections with or without the Sunnis. Osama bin Laden said anyone who will vote is an infidel. Just like in Afghanistan where the radical Sunni Taliban allied with bin Laden are still in their holes protesting the first freely elected leader in Afghanistan’s history, Zarqawi’s Sunni Muslims will just get more radical and blood thirsty with an alliance with bin Laden, boycott elections and live in their holes. Just like in Afghanistan, moderate Sunnis (like the moderate Sunni non Wahhabist Taliban) did not align themselves with terror and wanted to be part of the political process and laid down their arms and formed political opposition parties. Moderate Sunnis will do the same in Iraq. Sure some will stay home, but they know that the majority of the population made up of Kurds and Shiites (both previously gassed by Saddam) would rather die then not vote and will not risk staying out of the process. The Sunnis can see the writing on the wall.

Will Zarqawi’s terror continue? Unfortunately yes. How do we stop them? Hold free elections January 30^th and give the people a stake in their government. Zarqawi’s Sunni “insurgent” group is not an “insurgency” but just a radical group of terrorists opposed to democracy-as his alliance with bin Laden now proves. Let’s now call a spade a spade and label his group terrorists. His legitimacy has sunk even lower with moderate Sunnis with the bin Laden alliance. As democracy grows in Iraq over time Zarqawi’s group will diminish. It will take time, maybe years as it did in Afghanistan.

Lastly, we need not worry about Iran, the Shiite power next door. Elected moderate Iraqi Shiites will reject the radical non democratic and non Arab (Iranians are not Arabs, Iraqis are). Moderate Sunnis will come to the forefront and Zarqawi’s Sunni terrorists will eventually be isolated and marginalized, just like the radical Taliban were in Afghanistan. It took only three years in Afghanistan-give Iraq time.

Fast forward 10 years from now. A democratically elected free Afghanistan, Palestine and Iraq.

Politics

Tuesday, December 28, 2004 12:16:53 PM (Eastern Standard Time, UTC-05:00)
Comments [16]  |  Trackback

SQL Server 2005 introduces an enhancement to the OPENROWSET function, the new BULK rowset OLE DB provider. Via OPENROWSET, it lets you access data in files in a relational fashion. In the past, you used BULK INSERT to pull data from the file into a temp table and then ran your relational operations, but with SQL Server 2005, you can use the data contained in the file as part of your SQL statement rowset results. Keep in mind that you must specify a format file, which is the same format file you use with bcp.exe or the BULK INSERT statement. The following code shows how to access a file named c:\bulk.txt using the format file c:\bulk.fmt:

SELECT customerid, customername, totalorders

FROM OPENROWSET(BULK 'c:\bulk.txt',

       FORMATFILE = 'c:\bulk.fmt') AS (customerid, customername, totalorders)

SQL Server | Yukon Book

Wednesday, December 22, 2004 8:27:10 PM (Eastern Standard Time, UTC-05:00)
Comments [16]  |  Trackback