Import XML file to Database Table

In this article, I talked about how to parse XML or import XML to SQL Server Database Table.
XML File:

<br />
<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders><br />

To Query XML File we need to store in to xml variable @MyXML:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />

Now we use SQL query to get Order header from @MyXML:

<br />
SELECT Col.value('@OrderNumber', 'int') AS 'Order',<br />
Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
FROM @MyXML.nodes('/Orders/Order') AS T(Col)<br />

Now we use SQL query to get Order Items from @MyXML:

<br />
SELECT<br />
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,<br />
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

Full Code:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />
   SELECT<br />
    Col.value('@OrderNumber', 'int') AS 'Order',<br />
    Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)</p>
<p>   SELECT<br />
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,<br />
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

XML_To_DB

Thanks

Using REPLACE in an UPDATE statement

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
Normally, doing a search and replace in SQL is not radically difficult. You basically do an update using the replace() function. For example:

  Update Product
  Set Description = replace(Description, 'old text is this', 'new text will be this')    
  where Description like '%old text is this%' 

[ad#post]
However, if the Description column in your table is a ntext field, the above code is going to error out. The correct way around this is to cast() the column as a maximum-sized varchar() type. So the above will now look like the following:

    Update Product   
    Set Description = replace(cast(Description as varchar(8000)), 'old text is this', 'new text will be this')   
    where Description like ('%old text is this%') 

Thanks

Using Web Service (.NET) in Classic ASP via SOAP

Web services are a great tool to share data across many data sources within a single organization, even if the owners of the different sources don’t want to give others direct access to the data.
here I am showing how you can call web service in classic asp.
To start, create a Web Service in Visual Studio.NET or use existing web service.
here is my web service login.asmx coding file (login.cs),

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
///
/// Summary description for Login
/// <code>
[WebService(Namespace = "http://ashishblog.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[ScriptService]
public class Login : System.Web.Services.WebService {
[WebMethod]
public int LoginValidate(string email, string PW)
{
bool OK = false; 

// validate email or pW
if(email =="[email protected]" && PW=="ashish")
{
OK = true;
}
return OK;
}
}

Continue reading

Rollover effects using onmouseover and onmouseout events in html

I’ll show how to create Rollover effects using onmouseover and onmouseout event in html.

first create two CSS class

div.HoverON {

border:1px solid #FF2222;
}
div.HoverOFF {

border:1px solid white;
}

Now we use follow code to change HoverOFF to HoverON on onmouseover event and HoverON to HoverOFF on onmouseout event.

<div class="HoverOFF" onmouseover="this.className='HoverON'" onmouseout="this.className='HoverOFF'">
     Ashish's Blog
 </div>

Demo:

DEMO