MS TSQL for xml path question regarding xpath element

Question

Hi there,

I have the following TSQL statement:

select 
  tblName     "TblName",
  structure   "TblName/STRUCTURE",
  sqlRetrieve "TblName/SQLRETRIEVE",
  Identifier  "TblName/IDENTIFIER",
  '2'         "TblName/OBJECTTYPE"
from 
  configTable 
for xml path ('')

which outputs:

<TblName>PD_CODE_PRODUCTS
  <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
  <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
  <IDENTIFIER>DATA_OWNER</IDENTIFIER>
  <OBJECTTYPE>2</OBJECTTYPE>
</TblName>
<TblName>PD_two
  <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
  <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
  <IDENTIFIER>DATA_OWNER</IDENTIFIER>
  <OBJECTTYPE>2</OBJECTTYPE>
</TblName>

but I want to have this output (the element name :

<PD_CODE_PRODUCTS>
  <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
  <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
  <IDENTIFIER>DATA_OWNER</IDENTIFIER>
  <OBJECTTYPE>2</OBJECTTYPE>
</PD_CODE_PRODUCTS>
<PD_two>
  <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
  <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
  <IDENTIFIER>DATA_OWNER</IDENTIFIER>
  <OBJECTTYPE>2</OBJECTTYPE>
</PD_two>

Does anybody know how to achieve this with T-SQL?

Thanks
Daniel

Answer 1

I don't think you'll be able to achieve this with T-SQL, unfortunately.

The closest you could get is this:

<TABLE Name="PD_CODE_PRODUCTS">
  <TblName>
    <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
    <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
    <IDENTIFIER>DATA_OWNER</IDENTIFIER>
    <OBJECTTYPE>2</OBJECTTYPE>
  </TblName>
</TABLE>
<TABLE Name="PD two">
  <TblName>
    <STRUCTURE>PD_CODE_PRODUCTS</STRUCTURE>
    <SQLRETRIEVE>BATCHSP</SQLRETRIEVE>
    <IDENTIFIER>DATA_OWNER</IDENTIFIER>
    <OBJECTTYPE>2</OBJECTTYPE>
  </TblName>
</TABLE>

if you adapt your query to be:

select 
  tblName '@Name',
  structure   "TblName/STRUCTURE",
  sqlRetrieve "TblName/SQLRETRIEVE",
  Identifier  "TblName/IDENTIFIER",
  '2'         "TblName/OBJECTTYPE"
from 
  configTable 
for xml path ('TABLE')

Sorry I can't be of more help here - guess that's a feature Microsoft hasn't really considered (so far)! :-)

Marc

Answer 2

If you have any control over the XML structure I beg you not to do what you are trying to do. I've run into XML documents where the XML element names change based on data and it is horrible to work with. XPath statements are hard, XSLT is almost impossible.

Do what marc_s suggests, you'll thank him later.

If you have no control over the structure then you have my sympathies.

Answer 3

If I understood you correctly - you want one root element instead of having many main roots:

USE:

select tblName '@Name', structure "TblName/STRUCTURE", sqlRetrieve "TblName/SQLRETRIEVE", Identifier "TblName/IDENTIFIER", '2' "TblName/OBJECTTYPE"from configTable for xml path (''), root('TABLE'), type