Saturday, April 19, 2014

Transforms Rows Into XML Elements: FOR XML AUTO

This post we'll explore the XML AUTO mode.

FOR XML AUTO
    By default:
        No nesting
        Use attribute-centric presentation.
        Element/row tag names = schema.table or the table alias.
        It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.           Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.
                Example:
                SELECT custid
                          ,contactname
                FROM Sales.Customers
                FOR XML RAW('Customers');
By adding the ELEMENT directive, the xml result is nested and formatted using element-centric presentation.
WHEN joining multiple table:
     Use ORDER BY clause to group children elements under one parent element.
    The columns order in the SELECT and ORDER BY clause will influence the nesting of the XML result. I will include some examples for this on the next post.

Below are some examples.

--default presentation when no directive is specified
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO;
Result: attribute-centric presentation, no nesting, row/element tag name = schema.table
<Sales.Customers custid="1" contactname="Allen, Michael" />
<Sales.Customers custid="2" contactname="Hassall, Mark" />

--using table alias
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML AUTO;
Result: In this example, the row tag names = the table alias.
<c custid="1" contactname="Allen, Michael" /> 
<c custid="2" contactname="Hassall, Mark" />


-- with ELEMENTS directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS;
Result: By adding the ELEMENT directive, the XML result is nested and formatted using element-centric presentation.
<Sales.Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</Sales.Customers>
<Sales.Customers>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Sales.Customers>

-- with ELEMENTS and ROOT directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS, ROOT('Customers');
Result: we can add the ROOT directive to make the XML result well-formed.
<Customers>
  <Sales.Customers>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Sales.Customers>
  <Sales.Customers>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Sales.Customers>
</Customers>
--changing the row/element name by specifying an argument for AUTO.
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO('Customer'), ELEMENTS;
Result: This query fails. For AUTO mode, it's not allowed to change the row/element tag names by specifying an argument for AUTO mode. Note row/element tag name is only allowed with RAW or PATH mode of FOR XML.


Next post I will provide some example output a query result to XML when joining multiple tables.

Reference:
http://technet.microsoft.com/en-us/library/ms188273.aspx
MS 70-461 Training Kit

Monday, April 14, 2014

Create Table With Constraints

Let's take a break from XML. : )
To build a foundation for other topics(indexing, insert, update, merge) today we'll learn how to create a table. This is part of chapter 8, lesson 1 in 70-461 training kit. You can read more about the syntax here.

--
--Create customers and orders table on dbo schema of the AdventureWorks2012 sample database.
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Customers (
   custid    BIGINT NOT NULL PRIMARY KEY,
   lastname  VARCHAR(30) NOT NULL,
   firstname VARCHAR(30) NOT NULL 
);
GO
CREATE TABLE dbo.Orders (
   orderid BIGINT NOT NULL PRIMARY KEY,
   orderdate DATE NOT NULL ,
   totalamount DECIMAL(10,4) NOT NULL,
   returned CHAR(1) NOT NULL DEFAULT('N'), --indicate the order was returned by customer
   custid BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Customers(custid),
   CHECK (totalamount > 0.00), 
   CHECK (returned IN ('Y','N'))
);
GO

Notice that the dbo.Customers table has a primary key constraint. The dbo.Orders table has a primary key, 1 foreign key, 1 default and 2 check constraints. Let's write some queries to insert some records to the 2 tables above and explore about the constraints.
 
--
--This query fails because it doesn't specify firstname, 
-- but dbo.Customers.firstname column doesn't allow NULL mark.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES(1, 'Foster', NULL);
Result: Cannot insert the value NULL into column 'firstname', table 'AdventureWorks2012.dbo.Customers'; column does not allow nulls. INSERT fails. The statement has been terminated.
 
--
--This query fails because it violates the Primary Key constraint. 
--Every custid must be unique in the dbo.Customers table.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES
 (1, 'Foster', 'Jack')
,(1, 'Corby' , 'Larry');

Result: Violation of PRIMARY KEY constraint 'PK__Customer__973AFEFEABC60791'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1). The statement has been terminated.
 
--
--This query fails because totalamount < 0.00 
-- but dbo.Orders has a constraint for this totalamount > 0.00
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', -10.00, 'N', 1);

Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__totalamo__4F9CCB9E". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'totalamount'. The statement has been terminated.
 
--
--This query fails because returned value is not in ('Y','N').
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, '?', 1);
Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__returned__5090EFD7". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'returned'. The statement has been terminated.
--
-- This query fails because the dbo.Orders.custid column is a foreign key
--   that references the dbo.Customers.custid column,
--   but no customer with custid = 101 in the dbo.Customers.
-- Basically, in this case an order must have a customer exists in the dbo.Customers table.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, 'N', 101);
Result: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Orders__custid__4EA8A765". The conflict occurred in database "AdventureWorks2012", table "dbo.Customers", column 'custid'. The statement has been terminated.
--
-- This query fails because the can't insert a string into a column with a decimal data type.
-- Note data type is also considered as a constraint.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 'money', 'N', 101);
Result: Error converting data type varchar to numeric.

So, table constraints are rules to keep unwanted data out of a table.



Reference:
MS 70-461 Training Kit
http://technet.microsoft.com/en-us/library/ms174979.aspx

Sunday, April 13, 2014

FOR XML Clause: get XDR or XSD Schema only Without XML Data


Previous 2 posts, we learned about XMLDATA and XMLSCHEMA directive of the FOR XML clause.
These 2 directives returns the XDR(XML-Data Reduced) and XSD(XML Schema Definition) schema along with the XML data. In this post, we will learn how to get only the XDR or XSD schema without XML data.
--This query returns the XML-Data Schema only without the XML data
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  1 <> 1 -- this condition always evaluated to false, thus empty set is returned.
FOR    XML RAW
     , XMLDATA;
Result: notice that only the XDR schema is returned.
 
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="custid" dt:type="i4" />
    <AttributeType name="contactname" dt:type="string" />
    <attribute type="custid" />
    <attribute type="contactname" />
  </ElementType>
</Schema>
--This query returns the XSD Schema only without the XML data.
SELECT  custid, contactname, fax
FROM    Sales.Customers
WHERE   1 <> 1 -- this condition always evaluated to false, thus empty set is returned.
FOR XML RAW('Customer')
      , ELEMENTS XSINIL
      , ROOT('Customers')
      , XMLSCHEMA;
Result: Notice that only the XSD schema is returned.
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Customer">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="custid" type="sqltypes:int" nillable="1" />
          <xsd:element name="contactname" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="30" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="fax" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="24" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
</Customers>

Reference:
MS 70-461 Training Kit.

FOR XML RAW with XMLSCHEMA Directive

Today, we'll explore the XMLSCHEMA directive of the FOR XML clause.

XMLSCHEMA Directive
     Return XSD (XML Schema Definition) schema along with XML data of XML document/fragment.
     Can only use with RAW and AUTO mode.

SELECT  custid, contactname, fax
FROM    Sales.Customers
WHERE   custid <= 3
FOR XML RAW('Customer')
      , ELEMENTS XSINIL
      , ROOT('Customers')
      , XMLSCHEMA;

Result:
Notice the targetNamespce contains an Uniform Resource Name(URN) with format as: schemas-microsoft-com:sql:SqlRowSetn. Every time we execute the same query above in the same query window, n is incremented by 1. The sqltypes namespace is the link to SQL Server datatype to XSD type mapping.
 
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Customer">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="custid" type="sqltypes:int" nillable="1" />
          <xsd:element name="contactname" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="30" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="fax" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="24" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
    <fax>030-0123456</fax>
  </Customer>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
    <fax>(5) 456-7890</fax>
  </Customer>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>3</custid>
    <contactname>Peoples, John</contactname>
    <fax xsi:nil="true" />
  </Customer>
</Customers>

There're a lot more information about XSD. You can read more about XSD here.

Reference:
http://technet.microsoft.com/en-us/library/bb510461.aspx
http://technet.microsoft.com/en-us/library/ms175113.aspx
http://www.w3.org/TR/xmlschema11-1/

Friday, April 11, 2014

FOR XML RAW with XMLDATA Directive

Today we'll learn more about XMLDATA directive for the FOR XML clause. 

XMLDATA Directive
       Return XML-Data schema (XML-Data Reduced schema aka XDR schema) of XML document/fragment.
       Cannot be use with ROOT directive.
       Namespace identifier = Schema+n where n is an integer. Everytime we execute the same query, n is incremented by 1.
       Will be deprecated on future release of SQL Server. Recommendation: use XMLSCHEMA directive to return XSD schema instead.
      
--This query returns the XML-Data Schema of the XML result.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA;

--
--Result: Notice, if the same query above is executed one more time in the same query window,
--          the schema's name will change from Schema1 to Schema2.
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="custid" dt:type="i4" />
    <AttributeType name="contactname" dt:type="string" />
    <attribute type="custid" />
    <attribute type="contactname" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" custid="1" contactname="Allen, Michael" />
<row xmlns="x-schema:#Schema1" custid="2" contactname="Hassall, Mark" />


--
--The schema changes compared to result of previous query, when ELEMENTS directive is specified.
--ELEMENTS directive will changes the XML document/fragment's presentation 
--  from attribute-centric to element-centric presentation.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS;

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>


--
--This query will yield an error because ROOT directive cannot be used with XMLDATA directive.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS
     , ROOT; -- error, ROOT directive cannot be used with XMLDATA directive

Result:
Msg 6860, Level 16, State 1, Line 1
FOR XML directive XMLDATA is not allowed with ROOT directive or row tag name specified. 


If you know any other restrictions or uses of the XMLDATA directive for the FOR XML RAW, please share. Next post we'll learn about XMLSCHEMA Direction for the FOR XML RAW clause.

Reference:
http://technet.microsoft.com/en-us/library/bb510461.aspx
http://technet.microsoft.com/en-us/library/ms178035.aspx

Tuesday, April 8, 2014

Help for Beginners: Install SQL Server 2012 and AdventureWorks Sample Database


This post is for those that are brand new to SQL Server. I will guide you through how to set up a test environment for your database learning. After completing this tutorials you will have a SQL Server 2012 instance on your computer along with a sample database.

Step 1: You can download a 180 day trial version of SQL Server 2012 here, or spent $40 to get the developer version with all the feature here.

Step 2: Follow this instruction from Andy Leonard to install a default SQL Server Instance on your computer. Note make sure you follow this instruction exactly or else instruction on step 5 won't work.

Step 3: Download a copy of the AdventureWorks database here. This database will be used for your learning.

Step 4: Read the instruction below to launch SQL Server Management Studio(SSMS):
  Windows 7: go to Start menu > All Programs > Go to "Microsoft SQL Server 2102" folder > launch SQL Server Management Studio
  Windows 8: on your keyboard press this combo keys: Windows Key (the button with windows logo on your keyboard) + S, on the search box type: SQL Server Management Studio then launch the application.

Step 5: Connect to your computer default SQL Server Instance with the info below.
     Once the "Connect to Server" screen open, enter the info below
     Server Type: Database Engine
     Server Name: just type in a single period or MSSQLSERVER
     Authentication: Windows Authentication


Step 6: Watch this YouTube video from Katie for how to attach the AdventureWorkds database to your SQL Server Instance.



That's it. You now have a test environment for learning.

Now let's run a test query. The query below returns 100 people from the person table in the AdventureWorks2012 database. In your SQL Server Management Studio, open a new query window by pressing this combo keys: Ctrl + N
Type the query below and Press F5 to execute the query.

USE AdventureWorks2012;
GO

SELECT TOP(100) FirstName, LastName
FROM PERSON.PERSON;

Monday, April 7, 2014

Transforms Rows Into XML Elements: FOR XML RAW

In this post we'll learn how to transforms rows from a table or queries results to XML using the FOR XML RAW.
FOR XML RAW
By default:
No root element
Attribute-centric presentation (all data is display as attributes)
All row tag name = row
Exclude NULL mark
      Use  ELEMENTS XSINIL directive to include NULL marks.
Doesn't include nesting (XML document/fragment is format flat).

We'll go through a series of examples and explore the FOR XML RAW.

--
-- When no directive is specified after the RAW keyword, this query uses 
--   default setting above for the result: 
--     * use attribute-centric presentation(row data is stored inside row tag)
--     * no nesting
--     * no root node
--     * all XML elements' name = row
--     * NULL marks are excluded. 
USE TSQL2012;
GO

SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW;

Result: Notice that the result is flat - no nesting. 
<row custid="1" contactname="Allen, Michael" />
<row custid="2" contactname="Hassall, Mark" />


--
-- This query add nesting to the result by adding the ELEMENTS directive, root node. 
-- Notice we change row tag name from row to Customer. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('Customer')    -- Change the row tag name from row to Customer 
       ,ELEMENTS           -- Use element-centric. 
       ,ROOT('Customers'); -- Root node with name = Customers. 
                           -- Note Root node's name cannot be an empty string. 
                           -- If ELEMENT directive is used but exclude the ROOT directive,
                           --   the result is a XML fragment (has no root node). 
Result
<Customers>
  <Customer>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Customer>
  <Customer>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Customer>
</Customers>


-- If we specify an empty string for the RAW mode argument, 
-- all rows tag name will be removed. 
-- Note: In this case, we also need to include ELEMENT directive, 
-- else error will occurs. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ELEMENTS 
       ,ROOT('Customers');

Result:
<Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Customers>


-- This query will yield an error because by default XML RAW mode 
-- use attribute-centric presentation, and attributes are enclosed 
-- in the row tags. 
-- Therefore if the row tag are removed(by specified empty string for the RAW's argument)
--  and ELEMENT directive is not specified,
-- so there's no place to store the attributes.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ROOT('Customers');

Result: 
Msg 6864, Level 16, State 1, Line 1 
Row tag omission (empty row tag name) cannot be used 
    with attribute-centric FOR XML serialization.



--This query will yield an error because root tag name 
--   can't be an empty string.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,ROOT('');

Result:
Msg 6861, Level 16, State 1, Line 1
Empty root tag name cannot be specified with FOR XML.



-- This query include NULL marks and convert result to XML type.
SELECT  c.custid
       ,c.contactname
       ,c.fax
FROM    Sales.Customers AS c
WHERE   c.custid = 3
FOR XML RAW('Customer') -- Change the row tag name from row to Customer 
       ,ELEMENTS XSINIL -- Indent the XML elements, use element-centric, and include NULL mark 
       ,TYPE            -- Convert to result to XML type
       ,ROOT('Customers');

Result: Notice customer with custid equals 3, has missing fax number. 
 The result of missing fax is display as: <fax xsi:nil="true" />
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customer>
    <custid>3</custid>
    <contactname>Peoples, John</contactname>
    <fax xsi:nil="true" />
  </Customer>
</Customers>


--
-- This query uses XMLDATA directive to return the XML-Data schema that
--   describe the XML document structure.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,XMLDATA; -- return the XML-Data schema

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>




 
Reference:
Use RAW Mode with FOR XML: http://technet.microsoft.com/en-us/library/ms175140.aspx
70-461 Training Kit