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

I always want to learn SQL Server and XML file. Let us go over a 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:

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

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

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

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 another article. XML data is known as self-describing or self-defining, meaning that the structure of the data is embedded with the data, thus when the data arrives there is no need to pre-build the structure to store the data; it is dynamically understood within the XML.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL XML
Previous Post
SQL SERVER – Technical Articles – Performance Optimizations for the XML Data Type in SQL Server 2005
Next Post
SQL SERVER – Simple Example of Reading XML File Using T-SQL

Related Posts

125 Comments. Leave new

  • thanks for the Article… but i have a question..

    i want the attribute value with the path specified.. for eg:..

    sampleXML
    colors id=’Complementary Colors’
    color 1 color
    color 2 color
    colors
    colors id = ‘Analogous Colors’
    color 1 color
    color 2 color
    colors
    colors id = ‘Neutral Colors’
    color 1 color
    color 2 color
    colors
    sampleXML

    Reply
  • hello Pinal.
    I have a problem with the ISO date format:

    If I use the for xml with a date, you know, the result is an AUTO ISO conversion with a “T” as separator between date and time…
    eg:
    select GETDATE()
    for xml path

    2015-10-19T18:59:03.703

    So, i need a date without this “T” separator, just like 2015-10-19 18:59:03.703
    Do you have any idea?

    Thanks in advance.

    Reply
    • You can use CONVERT(CHAR(20),GETDATE(),120) function and choose format you need. I have a question to author. How to add namespace ONLY to header ??

      WITH XMLNAMESPACES (‘url’ as tns)

      SELECT ( SELECT ‘White’ AS [tns:Color1], …

      This one adds to colors, fruits and SampleXML. I do need add it only to ROOT
      Man thanks in advance

      Reply
  • Hi can someone explain how it is possible to import an xml file into database from http source?

    Reply
  • Hi Pinal, thank you, but your example is not creating an XML file, just create an XML Query output result. You need to use xp_cmdshell, and the bcp utility to store the query output to a XML file.
    I can create the XML file and store in a physical location but when open it in a web browser, a blank screen is displayed, no data.
    I notice , if open the XML file in Visual Studio, save, and then open it in a web browser, XML data is displayed correctly.
    What i need to do? Serialize the XML?
    Any suggestions?

    Reply
  • Hi,
    How can I add another level to your code between “SampleXML” and “Colors”?
    Thanks a ton!

    Reply
  • hi all, I am students, and I am working on my project. I have to create below requirements :

    1. Prepare the specifications of semi structured extensions to your Database. Minimal Requirements: 3 extensions in data model and 5 operations on XMP data. Each extension of data model should be described with: name, affected relations, the method of storage of XML used i.e. CLOB, XMLType, CHAR. Each description of operations on semi structured data should be described with: name, corresponding SQL operation from workload, tools used for implementation i.e. SAX, DOM, XPATH, XQUERY.

    2. Implementation of at least 3 semi structured extensions to data model.

    3.Implementation of at least 5 operations on semi structured data. Document the execution times with semi structured extensions.

    I created database and I use SQL SERVER 2012. So can any one tell me what should I do for these ?

    Plz give me good example and explain me ..thank you .

    Reply
  • Using t-sql to generate an XML file is still one of the most annoying things I come across on a weekly basis. Just because you can, doesn’t mean you should.

    Reply
  • Thanks for the Article…I want to write the query for below xml result, Is it possible ?

    for eg:..sampleXML

    White
    Blue
    Black
    Green
    Red

    White1
    Blue1
    Black1
    Green1
    Red1

    White11
    Blue11
    Black11
    Green11
    Red11

    Reply
  • Okay, then what do you recommend to generate XML files?

    I’m planing to use BCP to execute a stored proc.
    I do not want to write code in C#.
    Or use Powershell.
    Or use SSIS.

    Please?
    (ooops, almost forgot the magic word!)

    Reply

Leave a Reply