Here's today's issue. 

• I have an xml doc in SQL Server 2005. 
• I want to insert or replace a node value into this document. 
• I don't know in advance what value I'm trying to insert
• I don't even know if the node is empty or even if it exists.

You'd think that if I had a document that looked like this;

     DECLARE @XmlData xml

   SET @XmlData = '<Orders>   <Order id="W12345">      <OrderStatus>Shipped</OrderStatus>    </Order>    <Order id="T67890">       <OrderStatus>BackOrdered</OrderStatus>    </Order>    <Order id="P09876">       <OrderStatus />   </Order> </Orders>'

That I could do something like this;

SET @XmlData.modify('replace value of (/DocumentPacket/Party/Number/text())[3] with "Fraud" ')

Unfortunately replace only works if the node already has a value in it.  The solution, while, IMHO, unnecessarily verbose, it works and is pretty simple.

DECLARE @XmlData xml, @Found int, @value varchar(max)

SELECT @Found = 0, @value = 'Fraud', @XmlData =             '<Orders>               <Order id="W12345">                  <OrderStatus>Shipped</OrderStatus>               </Order>               <Order id="T67890">   <OrderStatus>BackOrdered</OrderStatus>               </Order>               <Order id="P09876">               <!--  --><!-- Test 1 No Node -->               <!--  <OrderStatus />   --><!-- Test 2 Empty Node -->               <!--  <OrderStatus>Any Value</OrderStatus> --><!-- Test 3 Node With Value -->               </Order>             </Orders>' 

--Node Doesn't ExistIF @XmlData.exist('(/Orders/Order/OrderStatus)[3]') = 0BEGIN    SET @Found =  1    SET @XmlData.modify('insert <OrderStatus>0000</OrderStatus> as first into (/Orders/Order)[3] ')   SET @XmlData.modify('replace value of (/Orders/Order/OrderStatus/text())[3] with sql:variable("@value") ')END--Node Exists but it's EmptyELSE IF @XmlData.value('(/Orders/Order/OrderStatus)[3]', 'varchar(max)') = ''BEGIN    SET @Found =  2   SET @XmlData.modify('insert text{0000} as first into (/Orders/Order/OrderStatus)[3] ')   SET @XmlData.modify('replace value of (/Orders/Order/OrderStatus/text())[3] with sql:variable("@value") ')END--Node Exists AND has a valueELSEBEGIN    SET @Found =  3   SET @XmlData.modify('replace value of (/Orders/Order/OrderStatus/text())[3] with sql:variable("@value") ')END --Let's see what we have...SELECT @XmlData AS XmlData, @Found as Found

Note that you cannot insert a dynamic value into an XML node in SQL Server 2005.  You must first insert the node with a dummy value and then replace the value with what you want.  In other words in the above example it would be nice if I could do this;

SET @XmlData.modify('insert <OrderStatus> sql:variable("@value")</OrderStatus> as first into (/Orders/Order)[3] ')

Instead of this;

SET @XmlData.modify('insert <OrderStatus>0000</OrderStatus> as first into (/Orders/Order)[3] ')SET @XmlData.modify('replace value of (/Orders/Order/OrderStatus/text())[3] with sql:variable("@value") ')  

Ok... I've been doing .Net for 6 years now and this is the first WebSite that I've actually uploaded all by my lonesome (Ok, I'm not counting basic html/css/javascript pages.   That's just uploading files.  And I'm not talking about playing around with LocalHost or somebody else's code.  This time I'm actually messing with IIS, although I guess it's still not my own code .)

Thanks BlogEngine.

I actually spent a fair amount of time evaluating different Blogging tools and code.  I didn't want to just begin a blog, I wanted to actually get my hands dirty in the Code.

Here's what we have to choose from in the .Net world; 

    SubText (formerly .Text)

    Das Blog

    BlogEngine.net 

    Community Server (also formerly .Text sort of)

Do a search on any of these and there's a wealth of all kinds of information and opinions.  I, personally would like to look at Community Server one day as a Family Web Page foundation but that's a blog for another day.

To me BlogEngine.net has a much more active and vibrant community.  The code was solid and easy to extend (of course i have ideas for extenstions) and implement.   DasBlog would have been my second choice and SubText my last.  Community Server never even got fully installed.  It is so much more than what I needed but it has a lot of potential.

For those out there trying to actually "Publish" BlogEngine.net instead of just copying files and getting started (wuss's) here's my scenario.

• Vista 64
• Visual Studio 2008
• BlogEngine.Net v 1.3.1
• IIS 7 Manager
  
• MyHosting.com on IIS 7
  

I uploaded the source and had it running on my machine almost mindlessly.  Within an hour I was experimenting with Themes and extensions.  It was almost trivial.  I like that in other peoples code.  I should have known it was too easy.

I've had my domain hosted on MyHosting.com for years and I was hosted on Server 2003.  I had to cancel my plan and open up a new one to get all the 2008 IIS 7 goodness.  I did this because of IIS Manager.  I liked the idea of using IIS Manager to manage my domain, applications and site functionality.  And once I'd gotten upgraded it was sweetness.   Full access to my hosted domain through IIS.  Who could ask for anything more.

So.  All that's left is to just copy the code over to the website right.  Wrong.  I couldn't just copy, I had to use VS 2008's publish functionality.  Now to be fair, i'm not sure if the publish broke IIS or if MyHosting just hadn't set up permissions correctly, but as soon as I uploaded my domain I lost all IIS Functionality and access to my domain from my browser.  It took 3 days for MyHosting to fix my permissions issues.

Welcome

Let me first say thanks for coming.  Feel free to explore and comment and vent (as long as it's healthy).

Let me say next that this post is NOT for you.  I don't know you, i will probably never meet you, and it will have no impact on my life at all.  This Blog is for me.  Let me explain... 

As I do my day to day coding, I, probably like you, come across everything from tidbits to head aches.    I sometimes don't learn my lessons well or I end up googling the same stuff multiple times.   From now on this will be my repository for code snippets and neat things that i will detail and tell you about.

Sooner or later you're going to google something and end up here.

Thanks for coming.  Come again.

Good luck and happy writing.

Louis Alston