Início / dba / Perguntas / 177716
Accepted
Raidenlee
Asked: 2017-07-01 11:41:53 +0800 CST

Coluna com aliases seletivos

  • 772

Desejo selecionar um alias com base em critérios de coluna específicos e gerar o XML necessário.

Digamos que temos uma tabela chamada ACCOUNTS:

Nome idade
--------- ---
John Doe 30
Mary Lane 40
Alice Bob 50

Consulta:

;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

A saída deve ser:

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

3 respostas

  1. Best Answer

    Por padrão, o SQL Server não cria um nó para valores NULL, portanto, se você alterar sua consulta para usar duas instruções CASE e retornar NULL quando não houver correspondência, obterá o XML que está procurando.

    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;

    dbfiddle

    • 5

  2. A mesma coluna com um nome diferente é SQL inválido, você precisa selecionar ambas as condições como colunas separadas:

    ;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

    Infelizmente, esta solução empurra os namespaces xml em todos os elementos.

    A melhor maneira de evitar isso, de acordo com este anwser https://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path parece estar entrando no mundo não tão maravilhoso deXML EXPLICIT

    Deixando-nos com:

    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

    Ambas as soluções também causam 2 varreduras na mesma tabela - simplificadas em explícito:

    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

relate perguntas