主页 / dba / 问题 / 177716
Accepted
Raidenlee
Asked: 2017-07-01 11:41:53 +0800 CST

具有选择性别名的列

  • 772

我想根据特定的列标准选择别名并输出所需的 XML。

假设我们有一个名为的表ACCOUNTS：

姓名年龄
---------- ---
约翰·多伊 30
玛丽巷 40
爱丽丝鲍勃 50

询问：

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT 
     CASE WHEN AGE <= 30 THEN 'BUYER' AS [def:GIRL]
          WHEN AGE >=40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE

输出应该是：

 <abc:OilCompany>
   <def:GIRL>BUYER</def:GIRL>
   <def:BOY>SELLER</def:BOY>
   <def:BOY>SELLER</def:BOY>
 </abc:OilCompany>
sql-server xml

3 个回答

  1. Best Answer

    默认情况下，SQL Server 不会为 NULL 值创建节点，因此如果您将查询更改为使用两个 CASE 语句并在没有匹配项时返回 NULL，您将获得您正在寻找的 XML。

    WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 
  'uri' AS def
)
SELECT CASE WHEN AGE <= 30 THEN 'BUYER' END AS [def:GIRL],
       CASE WHEN AGE >= 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;

    小提琴手

    • 5

  2. 同列不同名是无效SQL，需要将两种情况都选择为单独的列：

    ;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT 
(
  SELECT 'BUYER'
  FROM
  ACCOUNTS a
  WHERE AGE <=30
  FOR XML PATH ('def:GIRL'),TYPE
),
( SELECT 'SELLER'
  FROM
  ACCOUNTS
  WHERE AGE  >=40
  FOR XML PATH ('def:BOY'), TYPE
)
FOR XML PATH ('abc:OilCompany'),TYPE

    不幸的是，这个解决方案将 xml 名称空间推送到每个元素中。

    避免这种情况的最佳方法，根据这个 anwser https://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path仿佛步入了不那么美好的世界XML EXPLICIT

    留给我们：

    SELECT
    1 AS Tag
    ,NULL AS Parent
    ,'http://www.w3.org/2001/XMLSchema-instance' AS [abc:OilCompany!1!xmlns:abc]
    ,'uri' AS [abc:OilCompany!1!xmlns:def]
    ,NULL as [def:GIRL!2]
    ,NULL as [def:BOY!3]
  UNION ALL
  SELECT 
      2 as Tag
     ,1 as Parent
     ,NULL 
     ,NULL 
     ,'BUYER' as [def:GIRL!2]
     ,NULL
  FROM
    ACCOUNTS
  WHERE AGE <= 30
  UNION ALL
  SELECT
     3 as Tag
    ,1 as Parent
    ,NULL 
    ,NULL 
    ,NULL
    ,'SELLER' as [def:BOY!3]
  FROM
    ACCOUNTS
  WHERE AGE >= 40
FOR XML EXPLICIT

    这两种解决方案也会导致对同一张表进行 2 次扫描 - 显式简化：

    SELECT
  1 AS Tag
  ,NULL AS Parent
  ,'http://www.w3.org/2001/XMLSchema-instance' AS [abc:OilCompany!1!xmlns:abc]
  ,'uri' AS [abc:OilCompany!1!xmlns:def]
  ,NULL as [def:GIRL!2]
  ,NULL as [def:BOY!3]
UNION ALL
SELECT 
  CASE WHEN AGE <= 30 THEN 2 
       WHEN AGE >= 40 THEN 3 END as Tag
  ,1 as Parent
  ,NULL 
  ,NULL 
  ,CASE WHEN AGE <= 30 THEN  'BUYER' ELSE NULL END as [def:GIRL!2]
  ,CASE WHEN AGE >= 40 THEN 'SELLER' ELSE NULL END as [def:BOY!3]
 FROM
   ACCOUNTS
 WHERE AGE <=30 OR AGE >=40 -- required if there are other rows in the source table
FOR XML EXPLICIT
    • 3 
  3. ;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
cast(replace(cast(SELECT 
(
  SELECT 'BUYER'
  FROM
  ACCOUNTS a
  WHERE AGE <=30
  FOR XML PATH ('def:GIRL'),TYPE
) as varchar(1000)),'http://www.w3.org/2001/XMLSchema-instance','') as xml)   ,
( SELECT 'SELLER'
  FROM
  ACCOUNTS
  WHERE AGE  >=40
  FOR XML PATH ('def:BOY'), TYPE
)
FOR XML PATH ('abc:OilCompany'),TYPE
    • -4

相关问题