1

When using the following SQL From a single temp Table. 

Select  DataType as '@Datatype',
 (Select
 'Order' as [Property/@name],
 Order1 as Property,
 null,
  'ExtCode' as [Property/@name],
 ExtCode as Property,
 null
 from #TempLine
 for xml path('Properties'), type
 )
 from #TempLine
 Group by DataType
 for xml path('Object'), root('Objects')

I am getting the following XML 

 <Objects>
      <Object Datatype="ItemInfo">
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#ISSMS</Property>
        </Properties>
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#IMF</Property>
        </Properties>
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#ILZF</Property>
        </Properties>
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#L5898.5W</Property>
        </Properties>
      </Object>
    </Objects>

I Need to return the following XML with Object surrounding Properties.

 <Objects>
      <Object Datatype="ItemInfo">
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#ISSMS</Property>
        </Properties>
      </Object>
      <Object Datatype="ItemInfo">
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#IMF</Property>
        </Properties>
      </Object>
      <Object Datatype="ItemInfo">
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#ILZF</Property>
        </Properties>
      </Object>
      <Object Datatype="ItemInfo">
        <Properties>
          <Property name="Order">150825CREC004</Property>
          <Property name="ExtCode">150825CREC004#L5898.5W</Property>
        </Properties>
      </Object>
    </Objects>

I thought I could do it with embedded select but its not working out. I need to be able to have the object surround each of the properties and not the whole block.

A Johnston
  • 33
  • 1
  • 5
  • 1
    Realized had to change SQL. Thank you for your time. – A Johnston Aug 26 '15 at 08:32
  • Found answer to my question. Thank you for your time [Nesting example that worked for me.][1] [1]: http://stackoverflow.com/questions/25770689/sql-server-nesting-elements-with-for-xml-path – A Johnston Aug 26 '15 at 08:39

0 Answers0