Transform SELECT INTO HTML / XML / SQL SERVER / td=

Hi!

I have to say this is a little bit too hermetic for me, but it works.

This is an example of how to transform a SELECT into  HTML format.

Notice that it shows how to add color to each cell (td).

This example is priceless.

If you have any doubt, leave a comment.

Good luck!

SET @html = 

cast((

select

      td=NICK,'',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(unit AS MONEY) ,1)),'.00',''), '',

      td=RTRIM(CONVERT(CHAR(15) , CAST(sale AS MONEY) ,1)),'',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(TICKETS AS MONEY) ,1)),'.00',''), '',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(FLUXO AS MONEY) ,1)),'.00',''), '',

      td=PA, '',

      td=TM, '',

      td=CONVER, '',

      cast(case when GOAL_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=GOAL_P, '',

      cast(case when UNIT_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),UNIT_LY_DAY_P), '',

      cast(case when SALE_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),SALE_LY_DAY_P ),'',

      cast(case when TICKETS_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),TICKETS_LY_DAY_P), '',

      cast(case when FLUXO_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),FLUXO_LY_DAY_P), '', 

      cast(case when PA_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),PA_LY_DAY_P), '', 

      cast(case when TM_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),TM_LY_DAY_P), '', 

      cast(case when CONVER_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,1),CONVER_LY_DAY_P),''

FROM [table]

WHERE field LIKE '??%'

ORDER BY convert(numeric(11,1),GOAL_P) DESC

FOR XML path('tr'),type) as nvarchar(max))

+'</table>'

XML, XSD and DataSet - How to read xml and transform into a dataset

Hi!

To transform XML into a DataSet is really simple.

Step 1


Create a XML.

<xml>
        <car>
            <brand>honda</brand>
        </car>
        <car>
            <brand>ford</brand>
        </car>
        <car>
            <brand>volkswagen</brand>
        </car>
        <car>
            <brand>toyota</brand>
        </car>
        <car>
            <brand>chevrolet</brand>
        </car>
</xml>

Step 2

Create the XSD

<?xml version="1.0" encoding="Windows-1252"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="xml">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="cars">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="car">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="brand" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

How do I generate the XSD? If you are using Visual Studio, you're gonna find the following menu item

Step 3

Create a ConsoleApp project and use the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            DataSet lObjDataset = new DataSet();
            lObjDataset.ReadXmlSchema(@"C:\netdaniels\ConsoleApplication1\cars.xsd");
            lObjDataset.ReadXml(@"C:\netdaniels\ConsoleApplication1\cars.xml");

            Console.WriteLine("Table count:" + lObjDataset.Tables.Count.ToString());

            foreach (DataTable lObjDT in lObjDataset.Tables)
            {
               
                Console.WriteLine("Table name:" + lObjDT.TableName);

                foreach (DataColumn lObjDC in lObjDT.Columns)
                {
                    Console.WriteLine("Column name:" + lObjDC.ColumnName);
                }
            }
           
        }
    }
}

Et voilà! We're done!

The result is:

How simple is that?!

Há!!!