添加额外的节点 XML

Question

我正在使用 FOR XML PATH 提取一些数据。但是，我无法获得节点。预期成果：

<Users>
  <User UserId="1">
   <Profile>
     <PersonalDetails>
        <FirstName>Name1</FirstName>
        <LastName>Last1</LastName>
     </PersonalDetails>
     <Reward>0</Reward>
   </Profile>
 </User>
 <User UserId="2">
    <Profile>
      <PersonalDetails>
          <FirstName>Name2</FirstName>
          <LastName>Last2</LastName>
      </PersonalDetails>
      <Reward>0</Reward>
    </Profile>
 </User>
 <User UserId="3">
   <Profile>
      <PersonalDetails>
        <FirstName>Name3</FirstName>
        <LastName>Last3</LastName>
      </PersonalDetails>
      <Reward>0</Reward>
   </Profile>
 </User>
 <User UserId="4">
   <Profile>
    <PersonalDetails>
        <FirstName>Name4</FirstName>
        <LastName>Last4</LastName>
    </PersonalDetails>
    <Reward>0</Reward>
   </Profile>
 </User>
</Users>

让我用这个例子给你看：

DECLARE @User TABLE ( ID INT
                 ,FirstName VARCHAR(20)
                 ,LastName VARCHAR(20)
                 ,Reward BIT
                 ) 
INSERT INTO @User (ID, FirstName, LastName, Reward )
VALUES (1, 'Name1', 'Last1', 0)
      ,(2, 'Name2', 'Last2', 0)
      ,(3, 'Name3', 'Last3', 0)
      ,(4, 'Name4', 'Last4', 0)

    SELECT ID AS '@UserId'
            ,( SELECT FirstName AS 'FirstName'
                     ,LastName AS 'LastName'
               FROM @User
               WHERE ID = users.ID
               FOR XML PATH('PersonalDetails'), TYPE)       
           ,Reward AS 'Reward'
    FROM @User AS users
    FOR XML PATH('User'), ROOT('Users'), ELEMENTS 

这是我到目前为止得到的：

 <Users>
<User UserId="1">
   <PersonalDetails>
      <FirstName>Name1</FirstName>
      <LastName>Last1</LastName>
   </PersonalDetails>
   <Reward>0</Reward>
</User>
</User>
   <User UserId="2">
     <PersonalDetails>
       <FirstName>Name2</FirstName>
       <LastName>Last2</LastName>
     </PersonalDetails>
     <Reward>0</Reward>
   </User>
   <User UserId="3">
      <PersonalDetails>
         <FirstName>Name3</FirstName>
         <LastName>Last3</LastName>
      </PersonalDetails>
      <Reward>0</Reward>
   </User>
   <User UserId="4">
     <PersonalDetails>
       <FirstName>Name4</FirstName>
       <LastName>Last4</LastName>
     </PersonalDetails>
     <Reward>0</Reward>
   </User>
 </Users>

我无法添加额外的配置文件元素。我尝试使用 modify('首先插入 (/Users/User([1]'(，但输出也没有那么多。你能帮我这个吗？

Answer 1

您的数据在内部似乎很简单1:1：嵌套，但不1:n。这不需要子选择。使用 FOR XML PATH您可以直接指定嵌套：

DECLARE @User TABLE ( ID INT
                 ,FirstName VARCHAR(20)
                 ,LastName VARCHAR(20)
                 ,Reward BIT
                 ) 
INSERT INTO @User (ID, FirstName, LastName, Reward )
VALUES (1, 'Name1', 'Last1', 0)
      ,(2, 'Name2', 'Last2', 0);
SELECT ID AS [@UserId]
      ,FirstName AS [Profile/PersonalDetails/FirstName]  
      ,LastName AS [Profile/PersonalDetails/LastName]  
      ,Reward AS [Profile/Reward]
FROM @User AS u
FOR XML PATH('User'),ROOT('Users')

结果

<Users>
  <User UserId="1">
    <Profile>
      <PersonalDetails>
        <FirstName>Name1</FirstName>
        <LastName>Last1</LastName>
      </PersonalDetails>
      <Reward>0</Reward>
    </Profile>
  </User>
  <User UserId="2">
    <Profile>
      <PersonalDetails>
        <FirstName>Name2</FirstName>
        <LastName>Last2</LastName>
      </PersonalDetails>
      <Reward>0</Reward>
    </Profile>
  </User>
</Users>