Read xml file content or node value of xml file from sql server

Using "xp_cmdshell" command we can read the contents of xml file like below. Finally the variable @FileContents will have the contents of xml file.

DECLARE @FilePath varchar(255)
DECLARE @xCmd VARCHAR(255)
DECLARE @FileContents VARCHAR(MAX)

CREATE TABLE #temp(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(8000))

SET @FilePath = '\\ServerAddress.com\xmlFiles\xmlFile1.xml'
SET @xCmd = 'type ' + @FilePath
SET @FileContents = ''

INSERT INTO #temp EXEC master.dbo.xp_cmdshell @xCmd
DECLARE @x INT
DECLARE @y INT

SET @x = 0
SELECT @y = count(*) from #temp

WHILE @x <> @y
    BEGIN
        SET @x = @x + 1
        SELECT @FileContents = @FileContents + ThisLine from #temp WHERE PK = @x
    END

SELECT @FileContents as FileContents

There are different options available to read particular node value of xml file. I will explain them one by one. My default xml file will be in below format:

<?xml version="1.0" standalone="yes" ?>
<Price xmlns="http://tempuri.org/Price.xsd">
  <Rate>
     <ITEMNMBR>100</ITEMNMBR>
     <ITEMDESC>Audio Cassette</ITEMDESC>
     <QTY>1</QTY>
     <TPRICE>50</TPRICE>
  </Rate>
  <Rate>
     <ITEMNMBR>101</ITEMNMBR>
     <ITEMDESC>Video Cassette</ITEMDESC>
     <QTY>1</QTY>
     <TPRICE>100</TPRICE>
  </Rate>
</Price>

Method 1:

DECLARE @MyXML XML
SET @MyXML = @FileContents
SELECT  fileds.value('ITEMNMBR[1]', 'varchar(30)') AS ITEMNMBR,
        fileds.value('ITEMDESC[1]', 'varchar(30)') AS ITEMDESC,
        fileds.value('QTY[1]', 'varchar(30)') AS QTY,
        fileds.value('TPRICE[1]', 'varchar(30)') AS TPRICE
FROM    @MyXML.nodes('//Rate') as  xmldata(fileds)
WHERE fileds.value('ITEMNMBR[1]', 'varchar(30)') = '101'

Method 2:

CREATE TABLE #docs (pk INT PRIMARY KEY, xCol XML)
INSERT INTO #docs VALUES (1, @FileContents)

SELECT nref.value('ITEMNMBR[1]', 'nvarchar(50)') ITEMNMBR,
       nref.value('ITEMDESC[1]', 'nvarchar(50)') ITEMDESC,
       nref.value('QTY[1]', 'nvarchar(50)') QTY,
       nref.value('TPRICE[1]', 'nvarchar(50)') TPRICE      
FROM   #docs CROSS APPLY xCol.nodes('/Price/Rate') AS R(nref)
WHERE  nref.exist('.[ITEMNMBR = "100"]') = 1

Method 3:

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

   SELECT   *
   FROM   OPENXML (@idoc, 'Price/Rate', 2)
          WITH (ITEMNMBR  varchar(50) 'ITEMNMBR',
                ITEMDESC   varchar(50) 'ITEMDESC',
                QTY varchar(50) 'QTY',
                TPRICE varchar(50) 'TPRICE'                               
               ) R
   WHERE  R.ITEMNMBR = '101'

EXEC sp_xml_removedocument @idoc

SSRS - Clear multi-value cascading dropdown selected value

Most of us are facing problems that multi-value cascading dropdown selected value is not clearing when changing the value in parent dropdown.

For example I am having two dropdowns State and City in SSRS report. Based on State dropdown selected value, City dropdown values will be populated.

In my sample, State dropdown will have two values "Tamilnadu" and "Karnataka".

Cities for Tamilnadu will be "Trichy" and "C.City".

City for Karnataka will be "C.City".

Initially in State dropdown I will select Tamilnadu and in City dropdown I will select C.City.

Then if I select Karnataka in state dropdown city dropdown will be populated with C.City. But the problem is by default it will be selected as shown below.

But we may need that when it is populating newly, no values should be selected by default.


The solution is very simple.

In Report Parameters, city parameter has value field and label field as City. This is the root cause for this problem.

If I change the value field to state like below, the problem will be solved.

Because the state value is always changing. So the report will assume it as a different item. We can get the desired output.

Send Reminder mail based on Date field in SharePoint List

Below is the C# console application program to send reminder mail to user, seven days prior to due date.
It is used check WSS 3.0 list item and send mail.

Add Microsoft.SharePoint.dll in your project.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using System.Net.Mail;

namespace MailSending
{
    class Program
    {
        static string strMailFrom = "MailId";
        static string[] strMailTo = new string[] { "MailId", "MailId" };

        static void Main(string[] args)
        {
            SPSite oSite = new SPSite("SiteURLHere");
            SPWeb oWeb = oSite.OpenWeb();
            SPList oList = oWeb.Lists["ListNameHere"];

            for (int i = 0; i < oList.Items.Count; i++)
            {
                SPListItem oListItem = oList.Items[i];
                DateTime dtDate = Convert.ToDateTime(oListItem["Due Date"]);
                DateTime dtToday = DateTime.Today;
                TimeSpan TS = dtDate.Subtract(dtToday);
                if (TS.Days == 7)
                    SendMail(oList.Items[i].ID.ToString(), Convert.ToDateTime(oListItem["Due Date"]).ToShortDateString());
            }           
        }

        static void SendMail(string strListItemID, string strDueDate)
        {
            MailMessage message = new MailMessage();
            message.Subject = "This is Subject";
            message.From = new MailAddress(strMailFrom);

            for (int j = 0; j < strMailTo.Length; j++)
                message.To.Add(strMailTo[j]);

            string strMsgBody = "Please note that Due Date is " + strDueDate + ". Please check the below list for more information.
";
            strMsgBody += "" + strDueDate + ";

            message.Body = strMsgBody;
            message.IsBodyHtml = true;

            SmtpClient smtp = new SmtpClient("SMTP_Address_Here");
            smtp.Send(message);
        }
    }
}

After compiling we can take the exe file of this application and add in windows Task Scheduler. So that it will send mail to user.

Move Files between SharePoint document library

There are different options to move files between SharePoint document libraries. We can see them one by one.

Option 1 :

We can go to Explorer view, then copy paste the files to different location.

Here we can move multiple files. But Meta data like created by and created time will be changed.


Option 2 :

We can click the file --> Send To --> Other Location

Here Meta data will be retained. But we have to move one by one.

Option 3 :

We can write a C# application.
Add Microsoft.SharePoint.dll in your project.

    using Microsoft.SharePoint;

    private void button1_Click(object sender, EventArgs e)
    {
        SPSite sourceSite = new SPSite(SourceSiteURL);
        SPWeb sourceWeb = sourceSite.OpenWeb();
        SPSite destSite = new SPSite(DestinationSiteURL);
        SPWeb destWeb = destSite.OpenWeb();

        SPFolder oFolder = sourceWeb.GetFolder(SourceFolderName).SubFolders.Folder;
        SPFolder oDestFolder = destWeb.GetFolder(DestinationFolderName).SubFolders.Folder;

        foreach (SPFile oFile in oFolder.Files)
        {
            string strDestURL = oDestFolder.Url + "/";
            SPFile f = oFile;
            f.MoveTo(strDestURL + f.Name);
        }

        sourceWeb.Dispose();
        sourceSite.Dispose();
        destWeb.Dispose();
        destSite.Dispose();
    }


You can customize the code as you need files inside folder or subfolder.
Here we can move multiple files with meta data.

Expand Infragistics Grid using javascript

 Call the expandGrid function in onclientclick event of button.
  
    function expandGrid()
    {          
        var grid = igtbl_getGridById('<%=UltraWebGrid1.ClientID%>');      
        expand(grid);
    }

    function expand(grid)
    {           
      var rowsLength = grid.Rows.length;
        for (var i = 0; i < rowsLength; i++)
        {
            var rowObj = grid.Rows.getRow(i);
     
            if(rowObj.ChildRowsCount != 0)
            {           
                rowObj.setExpanded(true);
                expand(rowObj);
            }
            else
            {
                break;
            }
        }     
    } 

Format Infragistics Grid column for Currency and Export to Excel with same format

To Format the Hierarchical grid's column from integer to currency, below code can be used.

protected void UltraGrid1_InitializeLayout(object sender, LayoutEventArgs e)
{
      UltraGrid1.DisplayLayout.Bands[0].Columns[10].Format = "$ #####0.00";
}

If we export the grid to Excel using UltraWebGridExcelExporter, that column will be integer only. To get in currency format use below code.

In Page_Load add CellExported event like below.
this.UltraWebGridExcelExporter1.CellExported += new Infragistics.WebUI.UltraWebGrid.ExcelExport.CellExportedEventHandler(UltraWebGridExcelExporter1_CellExported);

Code for CellExported event will be like below.

void UltraWebGridExcelExporter1_CellExported(object sender, Infragistics.WebUI.UltraWebGrid.ExcelExport.CellExportedEventArgs e)
{
    if (e.GridColumn.Format == "$ #####0.00")
   {            e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.FormatString =  "$ #####0.00";
   }
}

Convert Text file to XML file

Convert text file to xml file which has delimiter as tab.


char strDelimiter = '\t';
string strFilePath = "c:/Test.txt";
DataSet oDS = new DataSet();
DataTable oTable = new DataTable();
DataRow oRows;
int intCounter = 0;

oDS.DataSetName = "Data_Root";
oDS.Namespace = strFilePath;
oDS.Tables.Add("Table1");

StreamReader oSR = new StreamReader(strFilePath);

//Go to the top of the file
oSR.BaseStream.Seek(0, SeekOrigin.Begin);

//Add in the Header Columns
foreach (string strFields1 in oSR.ReadLine().Split(strDelimiter))
        oDS.Tables[0].Columns.Add(strFields1);
           
//Now add in the Rows
oTable = oDS.Tables[0];
while (oSR.Peek() > -1)
{               
        oRows = oTable.NewRow();
         foreach (string strFields2 in oSR.ReadLine().Split(strDelimiter))
         {
                    oRows[intCounter] = strFields2;
                    intCounter = intCounter + 1;
         }
         intCounter = 0;
         oTable.Rows.Add(oRows);
}
          
//Create XML file
oDS.WriteXml("c:/Text_To_XML.xml"); 

Read Tables Name from Access DataBase

OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader reader;
           
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:\\SampleDB.mdb;" + "Jet OLEDB:Engine Type=5");
con.Open();
DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null, null, null, "TABLE"});           

for (int y = 0; y < schemaTable.Rows.Count; y++)
       listBox1.Items.Add(schemaTable.Rows[y][2].ToString());

con.Close();

Read column names from Access database table

OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader reader;
           
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:\\SampleDB.mdb;" + "Jet OLEDB:Engine Type=5");
con.Open();
           
cmd = new OleDbCommand("SELECT * from Table1", con);           
reader = cmd.ExecuteReader();
int iColumns = reader.VisibleFieldCount;
for (int x = 0; x < iColumns; x++)
{                             
        listBox1.Items.Add(reader.GetName(x));               
}
con.Close();

Automation of Word Mail Merge using Windows Application

I have Template and database in C drive with name  Template.doc and SampleDB.mdb respectively.

Add Reference: Microsoft Word 11.0 Object Library  from COM tab

using Word = Microsoft.Office.Interop.Word;

Word.Application wrdApp;
Word._Document wrdDoc;
Object oTemplate = "c:\\Template.doc";
Object oMissing = System.Reflection.Missing.Value;
Object oFalse = false;
Object oTrue = true;
Word.MailMerge wrdMailMerge;          

// Create an instance of Word  and make it visible.
wrdApp = new Word.Application();
wrdApp.Visible = true;           

// Create MailMerge Data.                       
wrdDoc = wrdApp.Documents.Open(ref oTemplate, ref oMissing, ref oTrue, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
wrdDoc.Select();
wrdMailMerge = wrdDoc.MailMerge;
object oQuery = "SELECT Name,Address1,City from Table1";
string strDB = "c:\\SampleDB.mdb";
wrdDoc.MailMerge.OpenDataSource(strDB, ref oMissing, ref oMissing, ref oFalse, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oQuery, ref oMissing, ref oFalse, ref oMissing);
wrdMailMerge.SuppressBlankLines = true;

// Perform mail merge.
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref oFalse);

// Close the Template document.
wrdDoc.Saved = true;
wrdDoc.Close(ref oFalse, ref oMissing, ref oMissing);

// Release References.           
wrdMailMerge = null;
wrdDoc = null;
wrdApp = null;

Change BackColor for Listbox Items in Windows Application

Sample code to give alternate color for alternate items in listbox.
In my application lstBoxSelected is the name of listbox.

Write these two lines in page load event.
lstBoxSelected.DrawMode = DrawMode.OwnerDrawFixed;
lstBoxSelected.DrawItem += new DrawItemEventHandler(DrawListSelected);

DrawListSelected is the event for drawing backcolor for listitem.
        private void DrawListSelected(object sender, DrawItemEventArgs e)
        {
            //Draw ListBox Items
            Rectangle rect = new Rectangle();
            rect = e.Bounds;

            // Setup the stringformatting object
            StringFormat sf = new StringFormat();
            sf.Alignment = StringAlignment.Center;

            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            {
                // Fill Backcolor for selected item               
                e.Graphics.FillRectangle(Brushes.Orange, e.Bounds);
            }
            else
            {
                Brush b;  // Object used to define backcolor               
                b = Brushes.LightCyan;
                if (e.Index % 2 == 0)
                    b = Brushes.PowderBlue;
                else
                    b = Brushes.LightCyan;
                e.Graphics.FillRectangle(b, rect);
            }
            if (lstBoxSelected.Items.Count > 0)
            {
                e.Graphics.DrawString(lstBoxSelected.Items[e.Index].ToString().Replace("*","").Trim(), e.Font, Brushes.Black, rect, sf);
            }
        }

Custom Search Web part code for WSS 3.0

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.Search;
using Microsoft.SharePoint.Search.Query;

 

public void keywordQueryExecute(string strQueryText) //Pass the search text here

{

    using (SPSite _SPSite = new SPSite("http://IP ADDRESS HERE/sites/SITE NAME HERE/"))

    {

        using (FullTextSqlQuery _FullTextSqlQuery = new FullTextSqlQuery(_SPSite))

        {

            _FullTextSqlQuery.StartRow = 0;

            _FullTextSqlQuery.HighlightedSentenceCount = 3;

            _FullTextSqlQuery.KeywordInclusion = KeywordInclusion.AnyKeyword;

            _FullTextSqlQuery.ResultTypes = ResultType.RelevantResults;

            _FullTextSqlQuery.RowLimit = 5000;

            _FullTextSqlQuery.TrimDuplicates = true;

            _FullTextSqlQuery.EnableStemming = false;

            _FullTextSqlQuery.IgnoreAllNoiseQuery = true;

            _FullTextSqlQuery.SiteContext = new Uri(_SPSite.Url);

            if (SPSecurity.AuthenticationMode != System.Web.Configuration.AuthenticationMode.Windows)

                _FullTextSqlQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;

            else

                _FullTextSqlQuery.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;

            StringBuilder sbFullTextSqlQuery = new StringBuilder(string.Empty);

            sbFullTextSqlQuery.Append("SELECT ");

            sbFullTextSqlQuery.Append(" FileExtension,Title,Description,HitHighlightedSummary,Path,Author,Size,LastModifiedTime ");

            sbFullTextSqlQuery.Append("FROM ");

            sbFullTextSqlQuery.Append(" SCOPE() ");

            sbFullTextSqlQuery.Append("WHERE ");

            sbFullTextSqlQuery.Append(" Site= ");

            sbFullTextSqlQuery.Append(" '" + _SPSite.Url.ToString() + "' ");

            sbFullTextSqlQuery.Append(" AND ");

            sbFullTextSqlQuery.Append(" ( FREETEXT (DefaultProperties, '" + strQueryText + "') ");

            sbFullTextSqlQuery.Append(" OR ");

            sbFullTextSqlQuery.Append(" CONTAINS (*, '\"" + strQueryText + "*" + "\"')) ");

            sbFullTextSqlQuery.Append(" ORDER BY ");

            string strSort = " Rank Desc";

            sbFullTextSqlQuery.Append(strSort);

            //sbFullTextSqlQuery.Append(" Rank Desc");

            _FullTextSqlQuery.QueryText = sbFullTextSqlQuery.ToString();

            try

            {

                ResultTableCollection resultTables = _FullTextSqlQuery.Execute();

                if ((int)ResultType.RelevantResults != 0)

                {

                    ResultTable tblResult = resultTables[ResultType.RelevantResults];

                    if (tblResult.TotalRows == 0)

                    {

                        lblQueryResult.Text = "No Search Results Returned.";

                    }

                    else

                    {

                        ReadResultTable(tblResult);

                        lblQueryResult.Text = "";

                    }

                }

            }

            catch (Exception Ex)

            {

                lblQueryResult.Text = Ex.Message;

            }

        }

    }

}

public void ReadResultTable(ResultTable rt)

{

    // Create a DataSet and load the returned ResultTable into it.

    DataTable relResultsTbl = new DataTable();

    relResultsTbl.TableName = "Relevant Results";

    DataSet ds = new DataSet("resultsset");

    ds.Tables.Add(relResultsTbl);

    ds.Load(rt, LoadOption.OverwriteChanges, relResultsTbl);

    // Add the results to the DataGrid.

    fillResults(ds);

}

Finally the DataSet ds will have the result. You can bind to gridview or table.

Read or Write OLE File Property

Here are the steps to Read or Write the properties of files [Title, Subject, Author and Comments]

Download and install DsoFileSetup_KB224351_x86.exe from http://www.microsoft.com/downloads/details.aspx?FamilyId=9BA6FAC6-520B-4A0A-878A-53EC8300C4C2&displaylang=en

- Add reference to C:\DsoFile\dsofile.dll to your project (this is the default location)

 

private void Button1_Click(object sender, EventArgs e)
{
  //This is the PDF file we want to update.
  string strFilename = @"c:\Test.pdf";
  //Create OleDocumentProperties object.
  DSOFile.OleDocumentProperties dso = new DSOFile.OleDocumentProperties();
  dso.Open(strFilename, false, DSOFile.dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);


  dso.SummaryProperties.Title = "Test Title";
  dso.SummaryProperties.Subject = "Test Subject";
  dso.SummaryProperties.Author = "Test Author";
  dso.SummaryProperties.Comments = "Test Comments";


  dso.Save();
  dso.Close(false);
}

If you want run the exe of this application in other system, you have to register dsofile.dll.

For that copy dsofile.dll in that system and in command prompt navigate to the directory where the dsofile.dll is present and run the regsvr32 command like below.

regsvr32 dsofile.dll

Read Value of Control which is inside iframe

var my_frame = document.getElementById("frame1");       

var framecontent = my_frame.contentWindow || my_frame.contentDocument;      

var ctrl = framecontent.document.getElementById("txtBox");      

alert(ctrl.value);

Get CheckBox Text and Checked property using Javascript

function checkboxClick(obj)  {      var lbl = obj.nextSibling;      while(lbl.nodeName.toLowerCase()!='label')           lbl=lbl.nextSibling;         alert(lbl.innerHTML);            if (obj.checked)          alert('checked');      else          alert('not checked');  }             <asp:CheckBox ID="chk" runat="server" Text="Check"  onclick="checkboxClick(this);" />

SharePoint Site Restore from Command Prompt

Use below commands to restore sharepoint site using stsadm command.

Here I assume that backup file is stored in D drive with name backup.dat

1) CD C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN
2) stsadm.exe -o restore -url http://your site path here -filename D:\backup.dat -overwrite

After restore go to Central Administration page. In that click Application management menu. After that click Site collection Administrators which is under Sharepoint site management.

After that In primary site collection Administrator and secondary site collection administrator, you have to add the server administrator Id.

SharePoint Site Backup from Command Prompt

Use below commands to take backup of sharepoint site using stsadm command

1) CD C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN
2) stsadm.exe -o backup -url http://your site path here -filename D:\backup.dat -overwrite


So that backup file will be generated in D drive with name backup.dat

Hi Friends

This is My New Journey With Blogger.Com