In SQLServer 2012 TSQL, what's the difference of using XML RAW, XML AUTO and XML PATH

Question

As the title, all open minds are welcomed

I tested in my computer, the output seems to be the same.

For example.

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name FROM DBO.T_User
FOR XML AUTO

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name FROM DBO.T_User
FOR XML RAW

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name FROM DBO.T_User
FOR XML RAW, ELEMENTS

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name FROM DBO.T_User
FOR XML PATH('CUSTOMERS')

Solution 1

XML RAW : each row in the result set is taken as one element with your columns being the attributes.

Example:

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name 
FROM DBO.T_User
FOR XML RAW;

OUTPUT:

<row id="7801020202083" First_Name="John" Surname="Doe" />
<row id="9812150201082" First_Name="Samantha" Surname="Hill" />

XML AUTO : Table names are your elements

Example:

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name 
FROM DBO.T_User
FOR XML AUTO;

OUTPUT:

<DBO.T_USER id="7801020202083" First_Name="John" Surname="Doe" />
<DBO.T_USER  id="7801020202083" First_Name="John" Surname="Doe" />

XML Path :Table columns are passed as child elements.

Example:

USE BOB_DATABASE
SELECT ID, Name, First_Name, Last_Name 
FROM DBO.T_User
FOR XML PATH;

OUTPUT:

<row>
  <id>7801020202083</id>
  <First_Name>John</First_Name>
  <Surname>Doe</Surname>
</row>
<row>
  <id>7801020202083</id>
  <First_Name>John</First_Name>
  <Surname>Doe</Surname>
</row>

Please also check out this blog https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ for a better breakdown.

Solution 2

Unfortunately they really aren't the same. Look at how the nodes are laid out. Look at the attributes. There are subtle differences that have big implications on how the XML is going to be consumed. Perhaps you need to control the root element: ROOT('SomeElementName'). MSDN has a really comprehensive explanation of each of the syntax options. MSDN FOR XML. I have post some code that will help you play around with the differences. Also some of the syntax will have noticable changes only when you do a join in your code. Thereby helping you establish hierarchy.

IF OBJECT_ID('tempdb..#XmlTestTable') IS NOT NULL DROP TABLE #XmlTestTable
CREATE TABLE #XmlTestTable 
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
)
INSERT INTO #XmlTestTable (FirstName,LastName) VALUES
('John','Doe'),
('Jane','Doe'),
('Brian','Smith'),
('Your','Mom')

--YOUR TESTS
SELECT * FROM #XmlTestTable FOR XML AUTO
SELECT * FROM #XmlTestTable FOR XML RAW
SELECT * FROM #XmlTestTable FOR XML RAW, ELEMENTS
SELECT * FROM #XmlTestTable FOR XML PATH('Customers')

DROP TABLE #XmlTestTable

Solution 3

difference between raw and auto
-auto produces header names using table name, raw uses row (or you can override using raw('myname')

-If query has a join, auto creates sub sections for the join table

difference between raw and path

-@ symbol prefixed on your column name when using path populate in row header

-\ symbol prefixed on your column name when using path populate in new sections (same as joins do using auto but more flexible)

fab explanation here with easy to follow examples here: http://thinknook.com/sql-server-returning-xml-results-2012-12-01/

Comments

[LONG]

what if adding elements after for xml raw,like for xml raw,elements, any difference between this with the for xml path('')