CODEWITHSUNDEEP
sql-servert-sqlfor-xml
user66928
user66928

Reputation: 75

MS TSQL for xml path question regarding xpath element

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

Upvotes: 2

Views: 1848

Answers (3)

Joel
Joel

Reputation: 1

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

Upvotes: 0

Darrel Miller
Darrel Miller

Reputation: 142242

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.

Upvotes: 0

marc_s
marc_s

Reputation: 755321

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

Upvotes: 1

Related Questions