SQL SERVER – Simple Example of Creating XML File Using T-SQL

I always want to learn SQL Server and XML. Let us go over very simple example today about how to create XML using SQL Server.

Please also read related article here SQL SERVER – Simple Example of Reading XML File Using T-SQL.

Following is the XML which we want to create:

samplexml SQL SERVER   Simple Example of Creating XML File Using T SQL

T-SQL Script to generate above XML:

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
FOR
XML PATH('Colors'),
TYPE
),
(
SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
FOR
XML PATH('Fruits'),
TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')
GO

samplexml1 SQL SERVER   Simple Example of Creating XML File Using T SQL

Every XML has two elements.

1) Attributes and 2) Value.

In my above example color4 has attribute along with value. Make sure to specify attribute before the value is defined otherwise it will give error. We will talk about this in other article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Technical Articles – Performance Optimizations for the XML Data Type in SQL Server 2005

I always wanted to learn XML and its usage. My friend and fellow MVP Jacob Sebastian is expert in XML, so if you are interested in XML please visit his blog here. If you are interested in performance optimization for XML Data type in SQL Server following article is must read for you.

Performance Optimizations for the XML Data Type in SQL Server 2005 by  Shankar Pal, Babu Krishnaswamy, Vasili Zolotov, and Leo Giakoumakis – Microsoft Corporation

Articles covers following subjects.

  • Introduction
  • Data Modeling with the XML Data Type
  • Bulk Loading XML Data
  • Indexing XML Data
  • Query and Data Modification
  • Conclusion

You can download the same article in word format from here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – XML – Split a Delimited String – Generate a Delimited String

SQL Server MVP and my very good friend Jacob Sebastian has written two wonderful articles about SQL Server and XML. I encourage to read this two articles to anybody who are interested in learning SQL and XML.

How to generate a Delimited String using FOR XML PATH

XQuery Lab 19 – How to parse a delimited string?

I have previously written article about operation of creating delimited string using COALESCE and no XML but as per telephonic conversation with Jacob COALESCE is only good for one column where as XML can do many more magics.

Jacob has already explained articles in detail so I suggest to read them carefully and digest. If you are not much big on XML, you do not have to spend time on learning this XML. Just take the XML script and use it for your need.

If you are wondering what about two article do, here is simple explanation. First article explains how to convert Sample 1 to Sample 2 and later one explains how to convert Sample 2 to Sample 1.
Sample 1:
/*

CompanyID CompanyCode
———– ———–
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2

*/

Sample 2:
/*

CompanyID CompanyString
———– ————————-
1 1|2
2 1|2|3|4
3 1|2

*/

XML is very powerful and when combined with SQL Server it always unveils new side of RDBMS and DBMS.

Reference : Pinal Dave (http://blog.SQLAuthority.com)