XQuery Made Simple

Introduction

As I understand with my development career the XQuery is very important and Lot of Developer just not using it as they don’t like the complexity of XQuery. Here in this article I am trying to make it so simple that everyone can use it.

Hope it will be interesting.

The XML String that I Used

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

<Addresses>

<Address type="Office">

<Street>21 R Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>799008</Zip>

</Address>

<Address type="Home">

<Street>SRR Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>700016</Zip>

</Address>

</Addresses>

<Phones>

<Phone type="Mobile">8085554422</Phone>

<Phone type="Home">8085553399</Phone>

</Phones>

</Contact>

How to Learn it

Step – 1 [ Create the Base Table and Insert XML String ]

CREATE TABLE tbl_Employee

(EmpId      INT NOT NULL IDENTITY PRIMARY KEY,

 EmpDetails XML NOT NULL);

GO

INSERT INTO tbl_Employee

(EmpDetails)

VALUES('<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle></Middle>

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle></Middle>

<Last></Last>

</Name>

</Names>

<Addresses>

<Address type="Office">

<Street>21 R Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>799008</Zip>

</Address>

<Address type="Home">

<Street>SRR Street</Street>

<City>Kolkata</City>

<State>West Bangal</State>

<Zip>700016</Zip>

</Address>

</Addresses>

<Phones>

<Phone type="Mobile">8085554422</Phone>

<Phone type="Home">8085553399</Phone>

</Phones>

</Contact>');

Step – 2 [ Understanding the Value() Method ]

SELECT EmpDetails.value('(Contact/Names/Name)[1]', 'VARCHAR(50)')

       AS EmpName

FROM   tbl_Employee;

EmpName

JoydeepDas

(Contact/Names/Name)[1]

<Contact>

<Names>

<Name type="Good"> à 1

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common"> à 2

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

SELECT EmpDetails.value('(Contact/Names/Name)[2]', 'VARCHAR(50)')

       AS EmpName

FROM   tbl_Employee;

EmpName

Joy

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common"> à 2

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

Check with others in same way.

SELECT EmpDetails.value('(Contact/Addresses/Address)[1]',

                        'VARCHAR(50)') AS Addresses

FROM   tbl_Employee;

Addresses

21 R StreetKolkataWest Bangal799008

SELECT EmpDetails.value('(Contact/Addresses/Address)[2]',

                        'VARCHAR(50)') AS Addresses

FROM   tbl_Employee;

Addresses

SRR StreetKolkataWest Bangal700016

SELECT EmpDetails.value('(Contact/Phones/Phone)[1]', 'VARCHAR(50)')

       AS Phone

FROM   tbl_Employee;

Phone

8085554422

SELECT EmpDetails.value('(Contact/Phones/Phone)[2]', 'VARCHAR(50)')

       AS Phone

FROM   tbl_Employee;

Phone

8085553399

SELECT EmpDetails.value('(Contact/Names/Name/First)[1]',

                        'VARCHAR(50)') AS EmpName,

       EmpDetails.value('(Contact/Names/Name/Middle)[1]',

                        'VARCHAR(50)') AS MiddleName,

       EmpDetails.value('(Contact/Names/Name/Last)[1]',

                        'VARCHAR(50)') AS LastName

FROM   tbl_Employee;

EmpName      MiddleName        LastName

Joydeep                                          Das

SELECT EmpDetails.value('(Contact/Names/Name/First)[2]',

                        'VARCHAR(50)') AS EmpName,

       EmpDetails.value('(Contact/Names/Name/Middle)[2]',

                        'VARCHAR(50)') AS MiddleName,

       EmpDetails.value('(Contact/Names/Name/Last)[2]',

                        'VARCHAR(50)') AS LastName

FROM   tbl_Employee;

EmpName      MiddleName      LastName

Joy

Step – 3 [ Understanding QUERY() Method ]

It not returns the Values but returns the XML portion of string.

SELECT EmpDetails.query('Contact/Names/Name[@type="Good"]/First')

       AS EmpName,

       EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle')

       AS MiddleName,

       EmpDetails.query('Contact/Names/Name[@type="Good"]/Last')

       AS LastName

FROM   tbl_Employee;

EmpName                        MiddleName     LastName

<First>Joydeep</First>  <Middle />          <Last>Das</Last>

Contact/Names/Name[@type="Good"]/…

<Contact>

<Names>

<Name type="Good">

<First>Joydeep</First>

<Middle />

<Last>Das</Last>

</Name>

<Name type="Common">

<First>Joy</First>

<Middle />

<Last />

</Name>

</Names>

SELECT EmpDetails.query('Contact/Names/Name[@type="Common"]/First')

  AS EmpName,

       EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle')

       AS MiddleName,

       EmpDetails.query('Contact/Names/Name[@type="Common"]/Last')

       AS LastName

FROM   tbl_Employee;

EmpName                 MiddleName      LastName

<First>Joy</First>    <Middle />          <Last />

Step – 4 [ Understanding Text() Method ]

SELECT CONVERT(VARCHAR(50),  EmpDetails.query('Contact/Names/Name[@type="Good"]/First/text()')) 

AS EmpName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle/text()')) 

AS MiddleName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Last/text()')) 

AS LastName

FROM tbl_Employee;

EmpName            MiddleName           LastName

Joydeep Das

SELECT CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()')) AS EmpName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()')) AS MiddleName,

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()')) AS LastName

FROM tbl_Employee;

EmpName          MiddleName         LastName

Joy

Step – 5 [ Understanding Exist() Method ]

SELECT 

CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/First') = 1

THEN

CONVERT(VARCHAR(50),        EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()'))

ELSE 'Not Found' END AS EmpName,

CASE WHEN 

EmpDetails.exist('Contact/Names/Name[@type="Common"]/Middle') = 1

THEN

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()'))

ELSE'Not Found ' END AS MiddleName,

CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/Last') = 1

THEN

CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()'))

ELSE 'Not Found' END AS LastName

FROM tbl_Employee;

In our case we not find any ‘Not Found’ as the Tag Exists.

EmpName    MiddleName      LastName

Joy

Hope you like it.

Posted by: MR. JOYDEEP DAS

How to make the DE-Normalized DB for OLAP from OLTP

Introduction

In this article we are trying to convert Normalized architecture to DE-Normalized forms that support OLAP. We are trying to say to convert OLTP Data base to OLAP data base. To understand it properly we are taking a simple domain example like Sales Domain.

Hope it will be informative and it put some value in your professional career.

Understanding the Sales Domain in OLTP Environment

The Entity Details:

Entity: Sales Order Header

	Attributes
[PK]	Order Id
	Order Date
	Location Id	[FK].[Location Details]
	Customer Id	[FK].[Customer Details]

Entity: Sales Order Detail

	Attributes
[PK]	Order Id	[FK].[Sales Order Header]
[PK]	Item Cd	[FK].[Item Details]
[PK]	Srl No
	Quantity
	Rate
	Amount

Entity: Customer Details

	Attributes
[PK]	Customer Id
	Customer Name
	Customer Address
	Customer Group Cd	[FK].[Customer Group ]

Entity: Customer Group

	Attributes
[PK]	Customer Group Cd
	Group Name
	Group Description

Entity: Location Details

	Attributes
[PK]	Location Id
	Location Name
	Location Description

Entity: Item Master

	Attributes
[PK]	Item Cd
	Item Name
	Item Description
	Item Group Cd	[FK].[Item Group Details]

Entity: Item Group Details

	Attributes
[PK]	Item Group Cd
	Item Group Name
	Item Group Description

ERD Diagram:

How to convert it into De-Normalized OLAP DB

Step-1 [ Fact Table ]

It contains

1. Facts or Numbers which can be used in aggregate function
2. The Foreign Key references Key which is needed to connect with Dimension Table.

In our Example we can do like this

Fact-Customer Order Details

	Attributes
[PK]	Location Id	[FK].[Location Details]
[PK]	Order Id
[PK]	Item Cd	[FK].[Item Master]
[PK]	Srl No
[PK]	Order Date	[FK].[Dimension-Date]
[PK]	Customer Id	[FK].[Customer Details]
	Quantity	[Fact]
	Rate	[Fact]
	Amount	[Fact]

Step – 2 [ Making Dimensional Table ]

Dimensional table is just like a master table and it related with Fact Table by foreign key relation.

Dimension-Date

It a special dimension contains the Hierarchy of Year à Month à Quarter à Day etc. It basically slices your data according to calendar.

	Attributes
[PK]	Order Date
	Month
	Quarter
	Year

Dimension-Customer Details

	Attributes
[PK]	Customer Id
	Customer Name
	Customer Address
	Customer Group Cd	[FK].[Customer Group]

Dimension-Location Details

	Attributes
[PK]	Location Id
	Location Name
	Location Description

Dimension-Item Master

	Attributes
[PK]	Item Cd
	Item Name
	Item Description
	Item Group Cd	[FK].[Item Group Details]

Step - 3 [ Making Lookup Dimension Table ]

Lookup Dimension is used to support a specific Dimension table. It is not connected with Fact but connected with another dimension table.

Dimension-Customer Group

	Attributes
[PK]	Customer Group Cd
	Group Name
	Group Description

Dimension-Item Group Details

	Attributes
[PK]	Item Group Cd
	Item Group Name
	Item Group Description

Making the STAR Schema

Making Snow flex Schema

Hope you like it.

Posted By: MR. JOYDEEP DAS