Converting seconds to Minutes and Seconds mm:ss

-------------- --------------

DECLARE @secs INT
SET @secs = 94

SELECT 
CASE 
    WHEN LEN(CAST(CAST(@secs AS real)/60 AS int)) = 1 
    THEN '0' 
    ELSE '' 
END 
+ CAST(CAST(CAST(@secs AS real)/60 AS int) AS nvarchar(2)) 
+ ':' +
CASE 
WHEN LEN(@secs - (CAST(CAST(@secs AS real)/60 AS int)*60)) = 1 
    THEN '0' 
    ELSE '' 
END 
+ CAST(@secs - (CAST(CAST(@secs AS real)/60 AS int)*60) AS nvarchar(2)) 
AS MinutesWithSeconds

--Result: '01:34'

MyFax XML Web. Creating the XML file to be sent to MyFax.com

I needed to create a small prototype code for an application and send faxes from our interface using MyFax XML WebService. Here is only one of the steps: How to create the XML file to be sent to their WebService. I don't have any connection to a database but the intention is to send "n" amount of faxes created already in a database and send them automatically. Do not hesitate to contact me if you have any question/problem about this code.

    string CreateXMLFile(string base64PDF)
    {
        string strXMLFile = "";
        try 
        {
            XmlWriterSettings oSettings = new XmlWriterSettings();
            oSettings.Indent = true;
            oSettings.OmitXmlDeclaration = true;
            oSettings.Encoding = Encoding.UTF8;

            _myXMLFile = "MyFaxFile.xml";
            using (XmlWriter writer = XmlWriter.Create(MapPath(_myXMLFile), oSettings))
            {
                writer.WriteStartDocument(true);
                writer.WriteStartElement("single_fax_info", "http://www.protus.com");
                writer.WriteAttributeString("xmlns", "xsi", null, "http://www.w3.org/2001/XMLSchema-instance");
                writer.WriteStartElement("SchemaVersion");
                writer.WriteValue("1.2");
                writer.WriteEndElement();

                writer.WriteStartElement("login_key");
                writer.WriteStartElement("user_id");
                writer.WriteValue("12345"); //TO BE REPLACED WITH REAL VALUE
                writer.WriteEndElement();
                writer.WriteStartElement("user_password");
                writer.WriteValue("abcd123"); //TO BE REPLACED WITH REAL VALUE
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("single_fax_options");
                writer.WriteStartElement("billing_code");
                writer.WriteValue("cust 123"); //TO BE REPLACED WITH REAL VALUE
                writer.WriteEndElement();
                writer.WriteStartElement("from_name");
                writer.WriteValue(txtFrom.Text.ToString()); //TO BE REPLACED WITH VALUE FROM THE DATABASE
                writer.WriteEndElement();
                writer.WriteStartElement("tiff_image_flag");
                writer.WriteValue("false");
                writer.WriteEndElement();
                writer.WriteStartElement("resolution");
                writer.WriteValue("high");
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("fax_recipient");
                writer.WriteStartElement("fax_recipient_number");
                writer.WriteValue(txtFaxNumber.Text.ToString()); //TO BE REPLACED WITH VALUE FROM THE DATABASE
                writer.WriteEndElement();
                writer.WriteStartElement("fax_recipient_name");
                writer.WriteValue(txtTo.Text.ToString()); //TO BE REPLACED WITH VALUE FROM THE DATABASE
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("document_list");
                //START OF ONE OR MANY PDFs TO BE SENT, YOU WOULD NEED TO CREATE A LOOP HERE
                writer.WriteStartElement("document");
                writer.WriteStartAttribute("document_content_type");
                writer.WriteValue("application/pdf");
                writer.WriteEndAttribute();
                writer.WriteStartAttribute("document_encoding_type");
                writer.WriteValue("base64");
                writer.WriteEndAttribute();
                writer.WriteStartAttribute("document_extension");
                writer.WriteValue("pdf");
                writer.WriteEndAttribute();
                writer.WriteValue(base64PDF); //TO BE REPLACED WITH THE PDF DOC CONVERTED TO BASE64
                writer.WriteEndElement();
                //END OF ONE OR MANY PDFs TO BE SENT
                writer.WriteEndElement();

                writer.WriteEndElement();
                writer.WriteEndDocument();
                writer.Flush();
                writer.Close();

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(MapPath(_myXMLFile));
                StringWriter sWriter = new StringWriter();
                XmlTextWriter xWriter = new XmlTextWriter(sWriter);
                xmlDoc.WriteTo(xWriter);
                strXMLFile = sWriter.ToString();
            }
        }
        catch
        {
            throw;
        }
        return strXMLFile;
    }

XML Extractor

This query shows how to extract an array of values for a field inside of the XML string:

DECLARE @sInputList VARCHAR(MAX)
DECLARE @sDelimiter VARCHAR(MAX)
DECLARE @sDelimiter2 VARCHAR(MAX)

SET @sInputList = '<package><document copies="1" docgroupid="6" docid="1042" packagedocid="docd1042-0" rule="" selected="true" title="Document 1 - Doc Group: Group 1" xmlformid="-1"><document copies="1" docgroupid="4" docid="2189" packagedocid="docd2189-0" rule="" selected="true" title="Document 2 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="78" packagedocid="docd78-0" rule="" selected="true" title="Document 3 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2122" packagedocid="docd2122-0" rule="" selected="true" title="Document 4 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="10" docid="2057" packagedocid="docd2057-0" rule="" selected="true" title="Document 5 - Doc Group: Package" xmlformid="-1"><document copies="1" docgroupid="10" docid="2055" packagedocid="docd2055-0" rule="" selected="true" title="Document 6 - Doc Group: Package" xmlformid="-1"><document copies="1" docgroupid="5" docid="873" packagedocid="docd873-0" rule="" selected="true" title="Document 7 - Doc Group: Group 3" xmlformid="-1"><document copies="1" docgroupid="5" docid="246" packagedocid="docd246-0" rule="" selected="true" title="Document 8 - Doc Group: Group 4" xmlformid="-1"><document copies="1" docgroupid="4" docid="1843" packagedocid="docd1843-0" rule="" selected="true" title="Document 9 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="621" packagedocid="docd621-0" rule="" selected="true" title="Document 10 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="230" packagedocid="docd230-0" rule="" selected="true" title="Document 11 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2152" packagedocid="docd2152-0" rule="" selected="true" title="Document 12 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2002" packagedocid="docd2002-0" rule="" selected="true" title="Document 13 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2153" packagedocid="docd2153-0" rule="" selected="true" title="Document 14 - Doc Group: Group 2" xmlformid="-1"></document></document></document></document></document></document></document></document></document></document></document></document></document></document></package>'
SET @sDelimiter = ' docID="'
SET @sDelimiter2 = '"'

IF CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    SET @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) --) RETURNS @List TABLE (item VARCHAR(8000))

DECLARE @List TABLE (item VARCHAR(MAX))
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
    SET @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1)))
    IF CHARINDEX(@sDelimiter2,@sItem,0) <> 0
    SET @sItem=RTRIM(LTRIM(SUBSTRING(@sItem,1,CHARINDEX(@sDelimiter2,@sItem,0)-1)))
    SET @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    IF LEN(@sItem) > 0
    INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
BEGIN
    SET @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter2,@sInputList,0)-1)))
    INSERT INTO @List SELECT @sInputList -- Put the last item in
END

SELECT * FROM @List