SQL Server Catalog Views

SQL Server > Catalog Views

Catalog views return information that is used by the SQL Server Database Engine.

• It is recommend that you query the catalog views instead of system tables
• It is recommend using SELECT * FROM sys.catalog_view_name because the number of columns returned might change

List of catalog views:

• Database mail
• sys.dataspaces
• sys.schemas
• sys.columns

Check the Status of E-Mail Messages SQL Server

SQL Server > Catalog Views > Database mail > sysmail_faileditems

In this SQL example will find information about any emails could not send successfully.


SELECT fi.subject, fi.last_mod_date,lg.description
FROM
   dbo.sysmail_faileditems as fi
INNER JOIN dbo.sysmail_event_log AS lg
ON fi.mailitem_id = lg.mailitem_id

Duration between two dates in SQL Server example

SQL Server > Built-In Functions > DATEDIFF

Returns the number of date and time boundaries crossed between two specified dates.

Example:

Duration between two dates in SQL Server example

DECLARE 

  @date1 DATETIME
,@date2   DATETIME

SELECT
  @date1 = getdate(),
   @date2  = dateadd(day,1,getdate()),
  @date2  = dateadd(minute,5,@date2)

SELECT
  @date1,
  @date2,
  DATEDIFF(hh, @date1, @date2) as Hours,  
  DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @date1, @date2),@date1),@date2) as Minutes

Result:

AppendChild C# XML Example

C# > XML > AppendChild

AppendChild adds node to the end of the list of child nodes.

Example

XmlDocument doc = new XmlDocument();
doc.LoadXml(yourxml);
XmlNode root = doc.DocumentElement;
XmlElement elem = doc.CreateElement("price");
elem.InnerText = "100";
root.AppendChild(elem);

MessageBox.Show(root.OuterXml); 

SelectSingleNode C# XML Example

C# > XML > SelectSingleNode

SelectSingleNode selects the first node that matches the XPath expression.

Example

Select node based on attribute value

Products.xml

xml version='1.0'?>

<products xmlns="urn:products-schema">
  <product name="Product1">
    <price>100</price>
  </product>
  <product name="Product2">
    <price>200</price>
  </product>
</products>

C# Code

XmlDocument doc = new XmlDocument();

doc.Load(@"D:\Products.xml");

XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("pp", "urn:products-schema");

XmlElement root = doc.DocumentElement;

XmlNode product = root.SelectSingleNode("pp:product[@name='Product1']", nsmgr);

C# XML Examples

C# > XML

XML is Extensible Markup Language is a markup language. It defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. (read more on http://en.wikipedia.org/wiki/XML)

The System.Xml namespace provides standards-based support for processing XML.




Classes

• XmlDocument
• XmlWriter

Examples

• Create CDATA section
• SelectSingleNode returns null
• SelectSingleNode
• AppendChild

Why SelectSingleNode always returns null?

C# > XML > XmlNamespaceManager > SelectSingleNode null

Why SelectSingleNode always returns null?

Because is missing the XML namespace defined SelectSingleNode call.

Solution:

XmlDocument doc = new XmlDocument();

doc.Load(xmlfile);

XmlNamespaceManager nsMgr = new XmlNamespaceManager(doc.NameTable);

nsMgr.AddNamespace("ns", "urn:www.data.com/feed");

var node = doc.SelectSingleNode("ns:ParentNode/ns:ChildNode", nsMgr);