CODEWITHSUNDEEP
sql-serverxml
arcee123
arcee123

Reputation: 223

how to perform FOR XML in SQL Query without column name

I'm outputting a XML formatted response in a select statement.

The XML schema goes: Placemark->MultiGeometry->Polygon-><more children> I'm using existing data.

The table I am running with, let's call it table1, has a name (varchar datatype), style (varchar datatype), and geometry (XML datatype)

The values of the geometry field have pre-formatted xml already. The root tag for each value for all records is MultiGeometry.

Here's the problem.

If I perform: 

select name, style, geometry from table1 
for xml path('Placemark'), ELEMENTS

I receive this:

<Placemark>
      <Name>stuff</Name>
       <style>style stuff</style>
         <Geometry> 
            <MultiGeometry>...xml...</MultiGeometry>
         </Geometry>
</Placemark>

This fails schema, because the schema (kml schemas) require that MultiGeometry is the child tag inside PLacemark, and the schema does not recognize the <Geometry> tag.

I need this:

<Placemark>
    <Name>stuff</Name>
      <style>style stuff</style>
        <MultiGeometry>...xml...</MultiGeometry>
</Placemark>

but trying: 

select name, style, geometry as [ ] 
from table1 for xml path('Placemark'), ELEMENTS

fails the query.

How do I get to where I can put MultiGeometry in Placemark without a intermediate tag (Such as <Geometry>)?

Thanks much

Upvotes: 2

Views: 2167

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7692

Sure:

select name, style, geometry.query('.')
from table1
for xml path('Placemark'), type, ELEMENTS;

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

Maybe something like.....

SELECT CAST( 
          REPLACE(
              REPLACE(CAST((select name, style, [Geometry]  AS [ReplaceMe]
                            from Table1 
                             for xml path('Placemark'),ELEMENTS) AS VARCHAR(MAX))
                     , '<ReplaceMe>' , '')
               , '</ReplaceMe>' , '') AS XML)

Upvotes: 0

Related Questions