TSQL FOR XML EXPLICIT

Using xml: TSQL FOR XML EXPLICIT on newest questions tagged xml – Stack Overflow

Not able to to get the desired XML output

The following:

  SELECT  1 as Tag,
          0 as Parent,
          sID       as [Document!1!sID],
          docID     as [Document!1!docID],
          null      as [To!2!value]
  FROM docSVsys with (nolock)
  where docSVsys.sID = '57'
  UNION ALL
  SELECT 2 as Tag,
         1 as Parent,
         sID,
         NULL,
         value
  FROM   docMVtext
  WHERE  docMVtext.sID = '57'
  ORDER BY [Document!1!sID],[To!2!value]
  FOR XML EXPLICIT;

Produces:

    <Document sID="57" docID="3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA">
      <To value="Frank Ermis" />
      <To value="Keith Holst" />
      <To value="Mike Grigsby" />
    </Document>

What I want is:

    <Document sID="57">
      <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID>
      <To>
        <Value>Frank Ermis</Value>
        <Value>Keith Holst</Value>
        <Value>Mike Grigsby</Value>
      </To>
    </Document>

Can I get that ouput with FOR XML?

Ok I get they may be technically equivalent.
What I want and what I need are not the same.

Using xDocument for this is is SLOW.
There are millions of documents and need to XML up to 1 million at a time to XML.
The TSQL FOR XML is super fast.
I just need to get FOR XML to format.

The solution (based on accepted answer):

   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] >= '57'
  ORDER BY
      [sv].[sParID], [sv].[sID]
  FOR XML PATH('Document'), root('Documents')

Produces:

<Documents>
  <Document sID="57" sParID="57">
    <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA</docID>
    <addDate>2011-10-28T12:26:00</addDate>
    <To>
      <value>Frank Ermis</value>
      <value>Keith Holst</value>
      <value>Mike Grigsby</value>
    </To>
    <MVtest>
      <value>MV test 01</value>
      <value>MV test 02</value>
      <value>MV test 03</value>
      <value>MV test 04</value>
    </MVtest>
  </Document>
  <Document sID="58" sParID="57">
    <docID>3.818919.C41P3UKK00BRICLAY0AR1ET2EBPYSU4SA.1</docID>
    <addDate>2011-10-28T12:26:00</addDate>
  </Document>
  <Document sID="59" sParID="59">
    <docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA</docID>
    <addDate>2011-10-28T12:26:00</addDate>
    <To>
      <value>Vladimir Gorny</value>
    </To>
  </Document>
  <Document sID="60" sParID="59">
    <docID>3.818920.KJKP5LYKTNIODOEI4JDOKJ2BXJI5P0BIA.1</docID>
    <addDate>2011-10-28T12:26:00</addDate>
  </Document>
</Documents>

Now what I need to do is to add a DispName attribute to the element MVtext. Attribute cannot have any spaces and I would like to include the friendly name e.g. Multi Value Text.

source: http://stackoverflow.com/questions/8483936/tsql-for-xml-explicit
Using xml: using-xml