CODEWITHSUNDEEP
sql-serversqlxml
Hare Rama Hare Krishna
Hare Rama Hare Krishna

Reputation: 1115

Query results same when using xml raw,element and xml path

I am using sql-server 2012:

I have query like this:

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

SELECT * FROM #XmlTestTable FOR XML RAW, ELEMENTS --1
SELECT * FROM #XmlTestTable FOR XML PATH --2

Result of 1:

<row>
  <ID>1</ID>
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
</row>
<row>
  <ID>2</ID>
  <FirstName>Jane</FirstName>
  <LastName>Doe</LastName>
</row>
<row>
  <ID>3</ID>
  <FirstName>Brian</FirstName>
  <LastName>Smith</LastName>
</row>
<row>
  <ID>4</ID>
  <FirstName>Your</FirstName>
  <LastName>Mom</LastName>
</row>

Result of 2:

<row>
  <ID>1</ID>
  <FirstName>John</FirstName>
  <LastName>Doe</LastName>
</row>
<row>
  <ID>2</ID>
  <FirstName>Jane</FirstName>
  <LastName>Doe</LastName>
</row>
<row>
  <ID>3</ID>
  <FirstName>Brian</FirstName>
  <LastName>Smith</LastName>
</row>
<row>
  <ID>4</ID>
  <FirstName>Your</FirstName>
  <LastName>Mom</LastName>
</row>

both the queries give same results,?

How do I find out the difference and when to use xml path or xml elements Sql-server 2012 compiled on.

Upvotes: 0

Views: 1847

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

RAW mode transforms each row in the query result set into an XML element that has the generic identifier <row>, or the optionally provided element name.

Each column value in the rowset that is not NULL is mapped to an attribute of the <row> element, but if you've specified ELEMENTS option, columns values will be mapped to subelements, not attributes.

In PATH mode, column names or column aliases are treated as XPath expressions. These expressions indicate how the values are being mapped to XML. Each XPath expression is a relative XPath that provides the item type., such as the attribute, element, and scalar value, and the name and hierarchy of the node that will be generated relative to the row element.

So PATH mode gives you slightly more flexibility with output.

For example 

select FirstName as "Name/First", LastName as "Name/Last" from #XmlTestTable FOR XML PATH

will give you xml like

<row>
  <Name>
    <First>John</First>
    <Last>Doe</Last>
  </Name>
</row>
// the rest of xml skipped

and this query

select FirstName as "@FirstName", LastName as "@LastName" from #XmlTestTable FOR XML path

will give you xml in following format:

<row FirstName="John" LastName="Doe" />
<row FirstName="Jane" LastName="Doe" />
<row FirstName="Brian" LastName="Smith" />
<row FirstName="Your" LastName="Mom" />

Or you can even mix attributes and elements in your output, say like:

select 
    FirstName + ' '  + LastName as "Name/@Full", 
    FirstName as "Name/First", 
    LastName as "Name/Last" 
from #XmlTestTable FOR XML path

will give you

<row>
  <Name Full="John Doe">
    <First>John</First>
    <Last>Doe</Last>
  </Name>
</row>

In your particular case output from two queries exactly the same just because you haven't specified "special" column names for PATH mode and used ELEMENTS option for RAW mode.

See MSDN for reference about PATH and RAW modes.

Upvotes: 3

Related Questions