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

Question

As a name, all open minds are welcome

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

For instance.

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') 

Answer 1

XML RAW: Each row in the result set is treated as one element with your attribute columns.

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 children.

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 this blog https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ for the best breakdowns.

Answer 2

Unfortunately, they do not really match. See how the nodes are laid out. Look at the attributes. There are subtle differences that have big implications for how XML is consumed. Perhaps you need to control the root element: ROOT ('SomeElementName'). MSDN provides a truly detailed explanation of each syntax. MSDN FOR XML . I am posting some code to help you deal with the differences. In addition, some syntaxes will only have noticeable changes when you join your code. This helps you establish a 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 

Answer 3

difference between raw and auto
-auto creates header names using the table name, raw uses a string (or you can override using raw ('myname')

- If the query has a join, automatically creates subsections for the join table

difference between raw and path

- @ character prefix by the name of your column when using the path in the row header

- \ character prefix by the name of your column when using the path in new sections (the same as connections using auto, but more flexible)

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