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:

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

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

  • Hi Pinal, I have to create a xml file in the below mentioned format

    2011-08-15 00:00:00
    2011-10-19 07:53:25
    1

    15
    70
    1:R
    322.0000

    Can you please let me know on how to create this.

    Reply
  • 2011-08-15 00:00:00
    2011-10-19 07:53:25
    1

    15
    70
    1:R
    322.0000

    Reply
  • Hi.. I have a query .. Is it possible to change the xml element name ..?

    For eg: i have an xml like 1result ; i need to change this xml values as 1qwe;

    Reply
  • Hi.. I have a query .. Is it possible to change the xml element name ..? For eg: i have an xml like

    1result ;

    i need to change this xml values as

    1qwe;

    Reply
  • Hi ..

    I have a query. Is it possible to change the xml elementname based on node value?

    For eg:

    abcd
    67

    efgh
    69

    to,

    abcd
    67

    efgh
    69

    Reply
  • Hello Sir,

    I have an XSD(schema defination) and a DB Table . So using both table and XSD is it possible to create resule XML. Please do let me know.

    Reply
  • hello i have an error while executing this code
    select * from authors for xml auto,elements(‘authors’),root(‘table’);

    the error is

    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘authors’.

    please correct this and do me the favour

    Reply
  • #Prabha please modify your query as like this one

    select * FROM Product for XML AUTO, ROOT(‘Table’), ELEMENTS

    Reply
  • Hi,

    I have a question. How would you do the next exercises?:

    Reply
  • hi, i just need multiple path and my data should obtain by xml RAW..could u help me plz

    Reply
  • SCRIPT DOES NOT WORK REGARDLESS WHAT YOUR FRIENDS ARE SAYING

    Reply
  • I have created an XML file from the output of sqlcmd however, the XML file created can’t be opened with Internet Exprorer. Why?. How can we create XML File from SQL that can be opened with Internet explorer. Couple of tags are missing like xml version, encoding, Document type etc.. How can we add them while generating the XML File?.
    Rajan.S

    Reply
  • When trying to export the XML created, we get a ‘The operation could not be completed. Not enough storage is available to complete this operation.” error. Is there a way to export my xml query directly to an file? How can I export and avoid this error?

    Reply
  • Hello,

    what is the concept of XML warehouse?

    can i implement this using sql server?

    Reply
  • What are the steps to build xml warehouse?

    Reply
  • Thanks, this was a huge help.

    Reply
  • Thanks you

    Reply
  • HI pinal,
    I want to know to write a SP using XML using more table data

    Reply
  • How to generate below format using Sql Sever select query.

    Happy Lines

    OTHER
    cpp permit: 0123456

    NA
    123456789

    999 Carrier Rd
    BBBBB
    ABC
    94612

    INS101

    CA-DIR

    100010009
    H14343
    12-264

    Reply
  • vijendra Singh
    September 7, 2015 1:42 pm

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

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

    1
    2

    1
    2

    1
    2

    Reply

Leave a Reply

Menu
Exit mobile version