dimanche 26 avril 2015

How to use XML Auto to get the format obtained by XML Path in sql server


I am using sql server 2012.

This is my query:

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  FirstName as "Name/@FN",LastName  as "Name/@LN" from #XmlTestTable for xml path('X'),root('Y')

It gives results like this:

<Y>
  <X>
    <Name FN="John" LN="Doe" />
  </X>
  <X>
    <Name FN="Jane" LN="Doe" />
  </X>
  <X>
    <Name FN="Brian" LN="Smith" />
  </X>
  <X>
    <Name FN="Your" LN="Mom" />
  </X>
</Y>

How can I obtain this format using XML AUTO

select  FirstName as "Name/@FN",LastName  as "Name/@LN" from #XmlTestTable for xml auto

generates this:

<_x0023_XmlTestTable Name_x002F__x0040_FN="John" Name_x002F__x0040_LN="Doe" />
<_x0023_XmlTestTable Name_x002F__x0040_FN="Jane" Name_x002F__x0040_LN="Doe" />
<_x0023_XmlTestTable Name_x002F__x0040_FN="Brian" Name_x002F__x0040_LN="Smith" />
<_x0023_XmlTestTable Name_x002F__x0040_FN="Your" Name_x002F__x0040_LN="Mom" />

And could anybody tell me why I get the sting like _x002F__x0040_FN in above format?


Aucun commentaire:

Enregistrer un commentaire