title: TSQL FOR XML PATH Attribute On , Type
body: <p>Basically I need to output both single value and muti value fields from a database.  Most  users of the data are not technical and it would be nice if they could read the XML and make sense of it.  With help from another post I got almost all the way there.  </p>
  
  <p>The problem I have is that the element name cannot have any spaces.  Need to include a display name that may have spaces.  For example MVtext displayName="Multi Value Text".  If the right answer is an inline schema or another approach please let me know.  It is not like I can a publish static schema as the application can dynamically add fields. And the fields can change with the type of report.  The fields are in a table and I read that table to build the select.</p>
  
  <p>How can I communicate a display name that may have spaces for the elements?  SQL 2008 R2.</p>
  
  <pre><code>   SELECT top 4
       [sv].[sID] AS '@sID'
      ,[sv].[sParID] AS '@sParID' 
      ,[sv].[docID] AS 'docID'
      ,[sv].addDate as 'addDate'
      ,(SELECT [value] AS 'value'
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '113'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "To"  
      ,(SELECT [value] AS 'value'
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '130'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "MVtest" 
    FROM  [docSVsys] as [sv]
    WHERE [sv].[sID] &gt;= '57' 
    ORDER BY 
        [sv].[sParID], [sv].[sID]
    FOR XML PATH('Document'), root('Documents')
  </code></pre>
  
  <p>Produces:</p>
  
  <pre><code>&lt;Documents&gt;
    &lt;Document sID="57" sParID="57"&gt;
      &lt;docID&gt;3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA&lt;/docID&gt;
      &lt;addDate&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To&gt;
        &lt;value&gt;Frank Ermis&lt;/value&gt;
        &lt;value&gt;Keith Holst&lt;/value&gt;
        &lt;value&gt;Mike Grigsby&lt;/value&gt;
      &lt;/To&gt;
      &lt;MVtest&gt;
        &lt;value&gt;MV test 01&lt;/value&gt;
        &lt;value&gt;MV test 02&lt;/value&gt;
        &lt;value&gt;MV test 03&lt;/value&gt;
        &lt;value&gt;MV test 04&lt;/value&gt;
      &lt;/MVtest&gt;
    &lt;/Document&gt;
    &lt;Document sID="58" sParID="57"&gt;
      &lt;docID&gt;3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1&lt;/docID&gt;
      &lt;addDate&gt;2011-10-28T12:26:00&lt;/addDate&gt;
    &lt;/Document&gt;
    &lt;Document sID="59" sParID="59"&gt;
      &lt;docID&gt;3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA&lt;/docID&gt;
      &lt;addDate&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To&gt;
        &lt;value&gt;Vladimir Gorny&lt;/value&gt;
      &lt;/To&gt;
    &lt;/Document&gt;
    &lt;Document sID="60" sParID="59"&gt;
      &lt;docID&gt;3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1&lt;/docID&gt;
      &lt;addDate&gt;2011-10-28T12:26:00&lt;/addDate&gt;
    &lt;/Document&gt;
  &lt;/Documents&gt;
  </code></pre>
  
  <p>I tired </p>
  
  <pre><code>  SELECT  [value] AS 'value', 'MV test' as 'dispName'
  </code></pre>
  
  <p>But I get </p>
  
  <pre><code> &lt;MVtest&gt;
    &lt;value&gt;MV test 01&lt;/value&gt;
    &lt;dispName&gt;MV test&lt;/dispName&gt;
    &lt;value&gt;MV test 02&lt;/value&gt;
    &lt;dispName&gt;MV test&lt;/dispName&gt;
    &lt;value&gt;MV test 03&lt;/value&gt;
    &lt;dispName&gt;MV test&lt;/dispName&gt;
    &lt;value&gt;MV test 04&lt;/value&gt;
    &lt;dispName&gt;MV test&lt;/dispName&gt;
  &lt;/MVtest&gt;
  </code></pre>
  
  <p>SELECT  [value] AS 'value', 'MV test' as '@dispName'
  Throws a execution error
  Row tag omission (empty row tag name) cannot be used with attribute-centric FOR XML serialization</p>
  
  <p>Desired output: </p>
  
  <pre><code>  &lt;Documents&gt;
    &lt;Document sID="57" sParID="57"&gt;
      &lt;docID&gt;3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA&lt;/docID&gt;
      &lt;addDate&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To&gt;
        &lt;value&gt;Frank Ermis&lt;/value&gt;
        &lt;value&gt;Keith Holst&lt;/value&gt;
        &lt;value&gt;Mike Grigsby&lt;/value&gt;
      &lt;/To&gt;
      &lt;MVtest dispName="Multi Value Text"&gt;
        &lt;value&gt;MV test 01&lt;/value&gt;
        &lt;value&gt;MV test 02&lt;/value&gt;
        &lt;value&gt;MV test 03&lt;/value&gt;
        &lt;value&gt;MV test 04&lt;/value&gt;
      &lt;/MVtest&gt;
    &lt;/Document&gt;
    &lt;/Documents&gt;
  </code></pre>
  
  <p>Solution: </p>
  
  <pre><code>SELECT top 4
       [sv].[sID] AS '@sID'
      ,[sv].[sParID] AS '@sParID'
      ,'SV' as 'docID/@SVMV' 
      ,[sv].[docID] AS 'docID'
      ,'SV' as 'addDate/@SVMV'
      ,[sv].addDate as 'addDate'
      ,'Email To' as 'To/@DisplayName'
      ,'MV' as 'To/@SVMV'
      ,(SELECT [value] AS 'value'
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '113'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "To"
      ,'Multi Value Text Sample' as 'MVtext130/@DisplayName'
      ,'MV' as 'MVtext130/@SVMV'      
      ,(SELECT  [value] AS 'value' 
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '130'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "MVtext130" 
    FROM  [docSVsys] as [sv]
    WHERE [sv].[sID] &gt;= '57' 
    ORDER BY [sv].[sParID], [sv].[sID]
    FOR XML PATH('Document'), root('Documents') 
  </code></pre>
  
  <p>Solution output:</p>
  
  <pre><code>&lt;Documents&gt;
    &lt;Document sID="57" sParID="57"&gt;
      &lt;docID SVMV="SV"&gt;3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA&lt;/docID&gt;
      &lt;addDate SVMV="SV"&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To DisplayName="Email To" SVMV="MV"&gt;
        &lt;value&gt;Frank Ermis&lt;/value&gt;
        &lt;value&gt;Keith Holst&lt;/value&gt;
        &lt;value&gt;Mike Grigsby&lt;/value&gt;
      &lt;/To&gt;
      &lt;MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV"&gt;
        &lt;value&gt;MV test 01&lt;/value&gt;
        &lt;value&gt;MV test 02&lt;/value&gt;
        &lt;value&gt;MV test 03&lt;/value&gt;
        &lt;value&gt;MV test 04&lt;/value&gt;
      &lt;/MVtext130&gt;
    &lt;/Document&gt;
    &lt;Document sID="58" sParID="57"&gt;
      &lt;docID SVMV="SV"&gt;3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1&lt;/docID&gt;
      &lt;addDate SVMV="SV"&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To DisplayName="Email To" SVMV="MV" /&gt;
      &lt;MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /&gt;
    &lt;/Document&gt;
    &lt;Document sID="59" sParID="59"&gt;
      &lt;docID SVMV="SV"&gt;3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA&lt;/docID&gt;
      &lt;addDate SVMV="SV"&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To DisplayName="Email To" SVMV="MV"&gt;
        &lt;value&gt;Vladimir Gorny&lt;/value&gt;
      &lt;/To&gt;
      &lt;MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /&gt;
    &lt;/Document&gt;
    &lt;Document sID="60" sParID="59"&gt;
      &lt;docID SVMV="SV"&gt;3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1&lt;/docID&gt;
      &lt;addDate SVMV="SV"&gt;2011-10-28T12:26:00&lt;/addDate&gt;
      &lt;To DisplayName="Email To" SVMV="MV" /&gt;
      &lt;MVtext130 DisplayName="Multi Value Text Sample" SVMV="MV" /&gt;
    &lt;/Document&gt;
  &lt;/Documents&gt;
  </code></pre>
  
  <p>This is a great solution to my question as asked.  The one symptom of adding the attribute is that I get the element even if there are zero value rows.  Ideally it will not list the element if there are no values.  </p>
  
  <p>Tried a Case statement but even if I set the value to '' it displays the element. </p>
  
  <pre><code>    ,[MVtext130/@DisplayName] = 
       Case (select COUNT(*) FROM [docMVtext] 
                    WHERE [docMVtext].[sID] = [sv].[sID]
                    AND [docMVtext].[fieldID] = '130')
         when '0' then ''
         else 'Multi Value Text Sample'
       end 
  </code></pre>
  
  <p>Updated solution to not list element with no values: </p>
  
  <pre><code>    SELECT top 4
       [sv].[sID] AS '@sID'
      ,[sv].[sParID] AS '@sParID'
      ,'SV' as 'docID/@SVMV' 
      ,[sv].[docID] AS 'docID'
      ,'SV' as 'addDate/@SVMV'
      ,[sv].addDate as 'addDate'
      ,(select top(1) 'Email To'
        from [docMVtext] as C
        where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@DisplayName'
      ,(select top(1) 'MV'
        from [docMVtext] as C
        where C.[sID] = [sv].[sID] and c.fieldID = '113' ) as 'To/@SVMV'
      --,'Email To' as 'To/@DisplayName'
      --,'MV' as 'To/@SVMV'
      ,(SELECT [value] AS 'value'
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '113'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "To"
      ,(select top(1) 'Multi Value Text Sample'
        from [docMVtext] as C
        where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@DisplayName'
      ,(select top(1) 'MV'
        from [docMVtext] as C
        where C.[sID] = [sv].[sID] and c.fieldID = '130' ) as 'MVtext130/@SVMV'
      --,'Multi Value Text Sample' as 'MVtext130/@DisplayName'
      --,'MV' as 'MVtext130/@SVMV'    
      ,(SELECT  [value] AS 'value' 
         FROM  [docMVtext] as [mv]
         WHERE [mv].[sID] = [sv].[sID]
           AND [mv].[fieldID] = '130'
         ORDER BY [mv].[value]
         FOR XML PATH (''), type
       ) AS "MVtext130" 
    FROM  [docSVsys] as [sv]
    WHERE [sv].[sID] &gt;= '57' 
    ORDER BY [sv].[sParID], [sv].[sID]
    FOR XML PATH('Document'), root('Documents')
  </code></pre>