---------------------------------------------------------
--
--	XMToRelation Sample Schema
--
---------------------------------------------------------

USE [master]
GO
/****** Object:  Database [XMLToRelational]    Script Date: 09/20/2009 04:44:04 ******/
IF  NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'XMLToRelational')
CREATE DATABASE [XMLToRelational]
GO

USE [XMLToRelational]
GO
/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorList]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorList]
GO

Create Table dbo.AuthorList
(
	AuthorID int Primary Key,
	DOJ Datetime,
	UpdatedDate Datetime,
	AuthorProfile XML
);
GO

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Author]') AND type in (N'U'))
DROP TABLE [dbo].[Author]
GO
Create Table dbo.Author
(
  AuthorID int, --<AuthorID>1001</AuthorID>
  Email varchar(255), --<Email>author1@publisher.com</Email>
  FirstName varchar(100), --<FirstName>FirstName-1</FirstName>
  LastName varchar(100), --<LastName>LastName-1</LastName>
  Age int, --<Age>31</Age>
  Sex char(1), --<Sex>M</Sex>
  AuthorBookCount int,
  AuthorArticleCount int
)
GO

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorCompany]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorCompany]
GO
Create Table dbo.AuthorCompany
(
	AuthorID int,
	EmployeeID int, --<EmployeeID>2001</EmployeeID>
    CompanyName varchar(100), --<CompanyName>Company One</CompanyName>
	Street varchar(100), --<Street>Street-1</Street>
    City varchar(100), --<City>City-1</City>
    [State] varchar(25), --<State>State-1</State>
    ZipCode varchar(10), --<ZipCode>1001</ZipCode>
    ContactNumber varchar(12) --<ContactNumber>111-111-1111</ContactNumber>
);

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorBooks]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorBooks]
GO
Create Table dbo.AuthorBooks
(
	AuthorID int,
	BookID varchar(36), --<BookID>249DA9BD-2DCD-4FF2-BB87-170B17A58070</BookID>
	Title varchar(50), --<Title>Title-1</Title>
	YearPublished int, --<YearPublished>2001</YearPublished>
	PublisherName varchar(100), --<PublisherName>Publisher One</PublisherName>
    LatestEdition int, --<LatestEdition>2008</LatestEdition>
    CurrentPrice money, --<CurrentPrice>59.60</CurrentPrice>
	LowCostPrint varchar(3), --<LowCostPrint>Yes</LowCostPrint>
	CoAuthorCount int
);

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BookCoAuthors]') AND type in (N'U'))
DROP TABLE [dbo].[BookCoAuthors]
GO
Create Table BookCoAuthors
(
	AuthorID int,
	BookID varchar(36), --comes from /Author/Books/Book node
	CoAuthorID varchar(36), --comes from /Author/Books/Book/CoAuthors/Author node
	CoLocated varchar(3)
);

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorArticles]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorArticles]
GO
Create Table dbo.AuthorArticles
(
	AuthorID int,
	ArticleID varchar(36), --<ArticleID>4CE268B4-B74C-49F3-A241-3DCF6B093DCC</ArticleID>
	Title varchar(150), --<Title>Title-One</Title>
	Summary varchar(500), --<Summary>Title-One Summary</Summary>
	WordCount int, --<WordCount>2000</WordCount>
	IsPublished varchar(3) --<IsPublished>Yes</IsPublished>
)

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorArticlePublishers]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorArticlePublishers]
GO
Create Table AuthorArticlePublishers
(
	AuthorID int,
	ArticleID varchar(36),
	WebPublisherID varchar(36), --<WebPublisherID>26145DCA-2A94-4A45-BB3E-4F6ED2C26152</WebPublisherID>
	WebPublisherName varchar(255), --<WebPublisherName>Web Publisher One</WebPublisherName>
	WebPublisherURL varchar(255), --<WebPublisherURL>http://www.webpublisherone.com</WebPublisherURL>
	ArticleURL varchar(255), --<ArticleURL>http://www.webpublisherone.com/4CE268B4-B74C-49F3-A241-3DCF6B093DCC</ArticleURL>
	ArticleRating int --<ArticleRating>5</ArticleRating>
)

/****** Object:  Table [dbo].[Authors]    Script Date: 09/06/2009 01:26:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthorBenefits]') AND type in (N'U'))
DROP TABLE [dbo].[AuthorBenefits]
GO
Create Table AuthorBenefits
(
	AuthorID int,
	BenefitName varchar(255),
	BenefitValue int
    --<HospitalizationLimit>500000</HospitalizationLimit>
    --<MedicalConsultationLimit>12000</MedicalConsultationLimit>
    --<LifeInsuranceCoverage>1000000</LifeInsuranceCoverage>
    --<DependentMemberCount>5</DependentMemberCount>
);

---------------------------------------------------------
--
--	Sample Data Insert
--
---------------------------------------------------------

INSERT INTO [dbo].[AuthorList]
           ([AuthorID]
           ,[DOJ]
           ,[UpdatedDate]
           ,[AuthorProfile])
     VALUES
           (1101
           ,'2009-04-04'
           ,'2009-04-04'
           ,'<Author xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1">
  <AuthorID>1001</AuthorID>
  <Email>author1@publisher.com</Email>
  <FirstName>FirstName-1</FirstName>
  <LastName>LastName-1</LastName>
  <Age>31</Age>
  <Sex>M</Sex>
  <Company>
    <EmployeeID>2001</EmployeeID>
    <CompanyName>Company One</CompanyName>
    <Street>Street-1</Street>
    <City>City-1</City>
    <State>State-1</State>
    <ZipCode>1001</ZipCode>
    <ContactNumber>111-111-1111</ContactNumber>
  </Company>
  <Books>
    <Book>
      <BookID>249DA9BD-2DCD-4FF2-BB87-170B17A58070</BookID>
      <Title>Title-1</Title>
      <YearPublished>2001</YearPublished>
      <PublisherName>Publisher One</PublisherName>
      <LatestEdition>2008</LatestEdition>
      <CurrentPrice>59.60</CurrentPrice>
      <LowCostPrint>Yes</LowCostPrint>
      <CoAuthors>
        <Author AuthorID="1002">
          <CoLocated>Yes</CoLocated>
        </Author>
        <Author AuthorID="1003">
          <CoLocated>No</CoLocated>
        </Author>
        <Author AuthorID="1004">
          <CoLocated>Yes</CoLocated>
        </Author>
      </CoAuthors>
    </Book>
    <Book>
      <BookID>78087BF4-B652-416A-A33B-3F458633EB26</BookID>
      <Title>Title-2</Title>
      <YearPublished>2002</YearPublished>
      <PublisherName>Publisher One</PublisherName>
      <LatestEdition>2007</LatestEdition>
      <CurrentPrice>69.30</CurrentPrice>
      <LowCostPrint>No</LowCostPrint>
      <CoAuthors>
        <Author AuthorID="1003">
          <CoLocated>No</CoLocated>
        </Author>
        <Author AuthorID="1002">
          <CoLocated>Yes</CoLocated>
        </Author>
      </CoAuthors>
    </Book>
    <Book>
      <BookID>903DF7D6-8F2D-4C79-8374-165A7B244A2C</BookID>
      <Title>Title-3</Title>
      <YearPublished>2005</YearPublished>
      <PublisherName>Publisher Two</PublisherName>
      <LatestEdition>2008</LatestEdition>
      <CurrentPrice>34</CurrentPrice>
      <LowCostPrint>No</LowCostPrint>
      <CoAuthors>
        <Author AuthorID="1005">
          <CoLocated>Yes</CoLocated>
        </Author>
      </CoAuthors>
    </Book>
  </Books>
  <Articles>
    <Article>
      <ArticleID>4CE268B4-B74C-49F3-A241-3DCF6B093DCC</ArticleID>
      <Title>Title-One</Title>
      <Summary>Title-One Summary</Summary>
      <WordCount>2000</WordCount>
      <IsPublished>Yes</IsPublished>
      <WebPublishers>
        <WebPublisher>
          <WebPublisherID>26145DCA-2A94-4A45-BB3E-4F6ED2C26152</WebPublisherID>
          <WebPublisherName>Web Publisher One</WebPublisherName>
          <WebPublisherURL>http://www.webpublisherone.com</WebPublisherURL>
          <ArticleURL>http://www.webpublisherone.com/4CE268B4-B74C-49F3-A241-3DCF6B093DCC</ArticleURL>
          <ArticleRating>5</ArticleRating>
        </WebPublisher>
        <WebPublisher>
          <WebPublisherID>787CA254-333F-43EA-9A81-5C15A775B10A</WebPublisherID>
          <WebPublisherName>Web Publisher Two</WebPublisherName>
          <WebPublisherURL>http://www.webpublishertwo.com</WebPublisherURL>
          <ArticleURL>http://www.webpublishertwo.com/4CE268B4-B74C-49F3-A241-3DCF6B093DCC</ArticleURL>
          <ArticleRating></ArticleRating>
        </WebPublisher>
      </WebPublishers>
    </Article>
    <Article>
      <ArticleID>70A646A1-5673-4738-BAE7-387FAF71B062</ArticleID>
      <Title>Title-Two</Title>
      <Summary>Title-Two Summary</Summary>
      <WordCount>2500</WordCount>
      <IsPublished>Yes</IsPublished>
      <WebPublishers>
        <WebPublisher>
          <WebPublisherID>F6151BB9-B26E-45AA-A491-DB6BA376D3A0</WebPublisherID>
          <WebPublisherName>Web Publisher Three</WebPublisherName>
          <WebPublisherURL>http://www.webpublisherthree.com</WebPublisherURL>
          <ArticleURL>http://www.webpublisherthree.com/70A646A1-5673-4738-BAE7-387FAF71B062</ArticleURL>
          <ArticleRating>3</ArticleRating>
        </WebPublisher>
        <WebPublisher>
          <WebPublisherID>009C80CB-176F-4B9F-A91A-CD6200D2FAF6</WebPublisherID>
          <WebPublisherName>Web Publisher Four</WebPublisherName>
          <WebPublisherURL>http://www.webpublisherfour.com</WebPublisherURL>
          <ArticleURL>http://www.webpublisherfour.com/70A646A1-5673-4738-BAE7-387FAF71B062</ArticleURL>
          <ArticleRating>4</ArticleRating>
        </WebPublisher>
      </WebPublishers>
    </Article>
  </Articles>
  <Benefits>
	<Benefit Name="HospitalizationLimit" Value="225000"/>
	<Benefit Name="MedicalConsultationLimit" Value="22000"/>
	<Benefit Name="LifeInsuranceCoverage" Value="2200000"/>
    <Benefit Name ="MonthlyTelephone" Value ="2200"/>
  <Benefit Name="DependentMemberCount" Value="2"/>
  </Benefits>
</Author>')

------------------------------------------------------------------
--
-- XMLToRelational T-SQL Queries
--
------------------------------------------------------------------

USE XMLToRelational
GO

--Author
SELECT 
	   AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID]
      ,AuthorProfile.value('/Author[1]/Email[1]', 'varchar(255)') As [Email]
      ,AuthorProfile.value('/Author[1]/FirstName[1]', 'varchar(100)') As [FirstName]
      ,AuthorProfile.value('/Author[1]/LastName[1]', 'varchar(100)') As [LastName]
      ,AuthorProfile.value('/Author[1]/Age[1]', 'int') As [Age]
      ,AuthorProfile.value('/Author[1]/Sex[1]', 'char(1)') As [Sex]
      ,Cast(Cast(AuthorProfile.query('count(/Author/Books/Book)')As varchar(5)) As smallint) As [AuthorBookCount]
      ,Cast(Cast(AuthorProfile.query('count(/Author/Articles/Article)')As varchar(5)) As smallint) As [AuthorArticleCount]
FROM [dbo].[AuthorList]


--AuthorCompany
SELECT 
	   AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID]
      ,AuthorProfile.value('/Author[1]/Company[1]/EmployeeID[1]', 'int') As [EmployeeID]
      ,AuthorProfile.value('/Author[1]/Company[1]/CompanyName[1]', 'varchar(100)') As [CompanyName]
      ,AuthorProfile.value('/Author[1]/Company[1]/Street[1]', 'varchar(100)') As [Street]
      ,AuthorProfile.value('/Author[1]/Company[1]/City[1]', 'varchar(100)') As [City]
      ,AuthorProfile.value('/Author[1]/Company[1]/State[1]', 'varchar(25)') As [State]
      ,AuthorProfile.value('/Author[1]/Company[1]/ZipCode[1]', 'varchar(10)') As [ZipCode]
      ,AuthorProfile.value('/Author[1]/Company[1]/ContactNumber[1]', 'varchar(12)') As [ContactNumber]
FROM [dbo].[AuthorList]

--AuthorBooks

SELECT --This Outer Select statement will be required for doing any further joins of the result set in scenarios like incremental insert, update or delete.
	BookInfo.*
FROM
(
	SELECT 
				SingleDataRow.AuthorID,
				Convert(varchar(36),EachBook.query('data(BookID)')) As [BookID],
				Convert(varchar(50),EachBook.query('data(Title)')) As [Title],
				Cast(Convert(varchar(4),EachBook.query('data(YearPublished)')) As int) As [YearPublished], --Double Casting is required because with Query XML element cannot be converted directly to int
				Convert(varchar(100),EachBook.query('data(PublisherName)')) As [PublisherName],
				Cast(Convert(varchar(4),EachBook.query('data(LatestEdition)')) As int) As [LatestEdition], --Double Casting is required because with Query XML element cannot be converted directly to int
				Cast(Convert(varchar(20),EachBook.query('data(CurrentPrice)')) As money) As [CurrentPrice], --Double Casting is required because with Query XML element cannot be converted directly to money
				Convert(varchar(3),EachBook.query('data(LowCostPrint)')) As [LowCostPrint],
				Convert(varchar(36),EachBook.query('count(CoAuthors/Author)')) As CoAuthorCount			
	FROM   
				(
				SELECT 
					AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID], 
					AuthorProfile 
				FROM [dbo].[AuthorList]
				) AS SingleDataRow
	CROSS APPLY
			   SingleDataRow.AuthorProfile.nodes('/Author/Books/Book') AS BookList(EachBook)

) AS BookInfo

--BookCoAuthors

SELECT --This Outer Select statement will be required for doing any further joins of the result set in scenarios like incremental insert, update or delete.
	BookCoAuthorInfo.*
FROM
(
	SELECT
		BookInfo.AuthorID,
		BookInfo.BookID,
		Convert(varchar(30),EachBookCoAuthor.query('data(@AuthorID)')) As CoAuthorID,
		Convert(varchar(3),EachBookCoAuthor.query('data(CoLocated)')) As CoLocated	
	FROM
	(
		SELECT 
			SingleDataRow.AuthorID,
			Convert(varchar(36),EachBook.query('data(BookID)')) As [BookID],
			EachBook.query('CoAuthors') As [EachBookCoAuthors]
		FROM   
			(
			SELECT 
				AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID], 
				AuthorProfile 
			FROM [dbo].[AuthorList]
			) AS SingleDataRow
		CROSS APPLY
			SingleDataRow.AuthorProfile.nodes('/Author/Books/Book') AS BookList(EachBook)
	) AS BookInfo
	CROSS APPLY
		BookInfo.EachBookCoAuthors.nodes('/CoAuthors/Author') CoAuthorList(EachBookCoAuthor)
) As BookCoAuthorInfo


--AuthorArticles

SELECT --This Outer Select statement will be required for doing any further joins of the result set in scenarios like incremental insert, update or delete.
	ArticleInfo.*
FROM
(

	SELECT 
				SingleDataRow.AuthorID,
				Convert(varchar(36),EachArticle.query('data(ArticleID)')) As [ArticleID],
				Convert(varchar(150),EachArticle.query('data(Title)')) As [Title],
				Convert(varchar(500),EachArticle.query('data(Summary)')) As [Summary],
				Cast(Convert(varchar(6),EachArticle.query('data(WordCount)')) As int) As [WordCount], --Double Casting is required because with Query XML element cannot be converted directly to int
				Convert(varchar(3),EachArticle.query('data(IsPublished)')) As [IsPublished]
	FROM   
				(
				SELECT 
					AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID], 
					AuthorProfile 
				FROM [dbo].[AuthorList]
				) AS SingleDataRow
	CROSS APPLY
			   SingleDataRow.AuthorProfile.nodes('/Author/Articles/Article') AS ArticleList(EachArticle)

) AS ArticleInfo


--AuthorArticlePublishers

SELECT --This Outer Select statement will be required for doing any further joins of the result set in scenarios like incremental insert, update or delete.
	ArticlePublisherInfo.*
FROM
(
	SELECT
		ArticleInfo.AuthorID,
		ArticleInfo.ArticleID,
		Convert(varchar(36),EachArticlePublisher.query('data(WebPublisherID)')) As WebPublisherID,
		Convert(varchar(255),EachArticlePublisher.query('data(WebPublisherName)')) As WebPublisherName,
		Convert(varchar(255),EachArticlePublisher.query('data(WebPublisherURL)')) As WebPublisherURL,
		Convert(varchar(255),EachArticlePublisher.query('data(ArticleURL)')) As ArticleURL,
		Cast(Convert(varchar(6),EachArticlePublisher.query('data(ArticleRating)')) As int) As ArticleRating --Double Casting is required because with Query XML element cannot be converted directly to int
	FROM
	(
		SELECT 
			SingleDataRow.AuthorID,
			Convert(varchar(36),EachArticle.query('data(ArticleID)')) As [ArticleID],
			EachArticle.query('WebPublishers') As [EachArticlePublishers]
		FROM   
			(
			SELECT 
				AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID], 
				AuthorProfile 
			FROM [dbo].[AuthorList]
			) AS SingleDataRow
		CROSS APPLY
		   SingleDataRow.AuthorProfile.nodes('/Author/Articles/Article') AS ArticleList(EachArticle)
	) AS ArticleInfo
	CROSS APPLY
		ArticleInfo.EachArticlePublishers.nodes('/WebPublishers/WebPublisher') WebPublisherList(EachArticlePublisher)
) As ArticlePublisherInfo


--AuthorBenefits
SELECT
	SingleRow.AuthorID,
	Convert(varchar(36),EachBenefit.query('data(@Name)')) As BenefitName,
	Cast(Convert(varchar(36),EachBenefit.query('data(@Value)')) As int) As BenefitName
FROM
(
	SELECT 
			AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID],
			AuthorProfile
	FROM dbo.AuthorList
) As SingleRow
CROSS APPLY
	SingleRow.AuthorProfile.nodes('/Author/Benefits/Benefit') BenefitList(EachBenefit)


GO
--Different variation of AuthorBenefits query using FLWOR style query
SELECT
	SingleRow.AuthorID,
	Convert(varchar(36),EachBenefit.query('data(@Name)')) As BenefitName,
	Cast(Convert(varchar(36),EachBenefit.query('data(@Value)')) As int) As BenefitName
FROM
(
	SELECT 
			AuthorProfile.value('/Author[1]/AuthorID[1]', 'int') As [AuthorID]
			,AuthorProfile.query('
			for $AuthorBenefitList in /Author[1]/Benefits[1]/Benefit
				return $AuthorBenefitList
			') As AuthorBenefitsColumn
	FROM dbo.AuthorList
) As SingleRow
CROSS APPLY
	SingleRow.AuthorBenefitsColumn.nodes('/Benefit') BenefitList(EachBenefit)