SQL for xml path('')

现在有一张表

SELECT * FROM @hobby FOR XML PATH

结果：

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

 

列节点如何改变？

SELECT hobbyID as 'MyCode',hName as 'MyName' FROM @hobby FOR XML PATH('MyHobby')

结果：

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

 

SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')

结果：

[ 爬山 ][ 游泳 ][ 美食 ] 

  

将SQL for xml path('')中转义的字符正常显示

 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

 

1.  select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('')
   运行上面这句将生成结果
   1a2b
   
   所有数据都生成一行，而且还没有连接字符，稍做变化：
   select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('')
   生成结果
   1,a;2,b;
   
   select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('')
   生成结果
   {1,"a"}{2,"b"}

    

 

SELECT tab.*,ISNULL(tab.ClientNames,cl.ClientName) AS ClientNameStr
                                FROM
                                (
                                    SELECT st.*,
                                           au.UserID,
                                           au.ClientCode,
                                           au.UserName,                                          
                                           STUFF(
                                           (
                                               SELECT DISTINCT
                                                      ',' + ClientName
                                               FROM
                                               (
                                                   SELECT csp.StoreId,
                                                          cl.ClientName
                                                   FROM dbo.CRM_StoreParent csp
                                                       LEFT JOIN CRM_Client cl
                                                           ON csp.ClientCode = cl.ClientCode
                                                              AND cl.FDelete = 0
                                               ) tt2
                                               WHERE tt2.StoreId = st.StoreId
                                               FOR XML PATH('')
                                           ),
                                           1,
                                           1,
                                           ''
                                                ) AS ClientNames
                                    FROM T_Store st
                                        LEFT JOIN dbo.Accounts_Users_CMS au
                                            ON st.StoreId = au.StoreID
                                    WHERE au.AuditStatus = 1 and st.IsTraditional=1
                                          AND au.StoreApplyId > 0
                                ) tab
                                    LEFT JOIN CRM_Client cl ON cl.ClientCode = tab.ClientCode AND cl.FDelete=0
                                WHERE 1 = 1

 

SELECT 
    p.ParentID,
    p.ParentName,
    -- 使用 FOR XML PATH 将子表的值合并成一个字符串
    STUFF((
        SELECT ';' + c.ChildValue
        FROM ChildTable c
        WHERE c.ParentID = p.ParentID
        FOR XML PATH('')
    ), 1, 1, '') AS ChildValues
FROM ParentTable p

 

,STUFF((SELECT ',' + D.[F_FullName] FROM [dbo].[Base_Departments] D WHERE CHARINDEX(D.[F_DepartmentId], t.[F_Departments]) > 0 FOR XML PATH('')), 1, 1, '') AS DepartmentNames