CODEWITHSUNDEEP
sql-serverxml
asphy1
asphy1

Reputation: 37

Skip NULL parameters in XML generated from SQL

What i'm trying to do?: generate XML from SQL Server 2014

Problem: some columns contain NULL - these columns act like attributes in my XML. My Validator does not accept an attribute if its value its empty/null.

Example:

number      Type        Trat        Frecv 
----------- ----------- ---------- ----------- 
31301879    NULL          1           2           
73229903      2          NULL         2           
73229903      2           1           2

Desired result

<polita number="31301879" Trat="1" Frecv="2"></polita>
<polita number="73229903" Type="2" Frecv="2"></polita>
<polita number="73229903" Type="2" Trat="1" Frecv="2"></polita>

!!! Disclaimer: i've read here the method of a variable table, but unfortunately it does not work for me because my columns need to be ATTRIBUTES and i get the duplicate attribute key

It looked like this

declare @t table
(
 col1,
 col2
)
SELECT 
col1,
'' as col1,
col2,
'' as col2
FROM @t

This is my code

   WITH    Inreg
              AS ( SELECT DISTINCT
                            *
                   FROM     dbo.C1_UE C1
                            INNER JOIN dbo.C2_UE C2 ON C1.nrp = C2.ContractNumber
                   ORDER BY C1.nrp
                            OFFSET @Page * @PageSize ROWS FETCH NEXT @PageSize
                            ROWS ONLY
                 ),
            TotalP
              AS ( SELECT   COUNT(ContractNumber) AS Nr_pers
                   FROM     dbo.C2_UE
                 ),
            TotalS
              AS ( SELECT   REPLACE(ROUND(SUM(C1.Val_capital)
                                          + SUM(C3.Suma3), 0), '.00', '') AS total
                   FROM     dbo.C1_UE C1
                            LEFT JOIN dbo.C3_UE C3 ON C1.nrp = C3.ContractNumber
                 )
        SELECT  REPLACE(( SELECT    '' as 1
                                    ( SELECT    TotalP.Nr_pers
                                      FROM      TotalP
                                    ) AS '@nr_pers' ,
                                    ( SELECT    TotalS.total
                                      FROM      TotalS
                                    ) AS '@total' ,
                                    ( SELECT *
       --pers
                                                ( SELECT 
                                                         *
                                                  FROM    @C2 C
                                                          INNER JOIN @C1 CC ON CC.nrp = C.ContractNumber

                                                  WHERE   CC.nrp = C1.nrp
                                                FOR
                                                  XML PATH('pers') ,
                                                      TYPE
                                                ) ,
      --acc
                                                ( SELECT *
                                                  FROM    @C3 c
                                                          INNER JOIN @C1 CC ON CC.nrp = c.ContractNumber
                                                  WHERE   CC.nrp = C1.nrp
                                                FOR
                                                  XML PATH('even') ,
                                                      TYPE
                                                )
                                      FROM      @C1 C1 
                                    FOR
                                      XML PATH('poli') ,
                                          TYPE
                                    )
                        FOR
                          XML PATH('decl400')
                        ), '<decl400',
                        '<decl400 xmlns="mfp:aa:dd:d403:dec:v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="mfp:mfp:aa:dd:d403:dec:v1 file:/C:/Users/a/Desktop/dec.xsd" ');

@C1, @C2 and @C3 are tables that are declared above, also @pageSize and @pageNumber are fixed values.

Upvotes: 2

Views: 1133

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Shnugo's was my first answer (+1), but another alternative if you don't want to specify all the fields is to use XML RAW. For example

Declare @YourTable table (number int,[Type] int,Trat int,Frecv int)
Insert Into @YourTable values
 (31301879, NULL,1   , 2) 
,(73229903, 2   ,NULL, 2) 
,(73229903, 2   ,1   , 2) 

Select * from @YourTable for XML RAW('polita')

Returns

<polita number="31301879" Trat="1" Frecv="2" />
<polita number="73229903" Type="2" Frecv="2" />
<polita number="73229903" Type="2" Trat="1" Frecv="2" />

EDIT - Thanks Shnugo for the RAW('polita')

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

What you need - if I got this right - is the standard behaviour:

DECLARE @Dummy TABLE(number INT,Type INT,Trat INT,Frecv INT);
INSERT INTO @Dummy VALUES
 (31301879,NULL,1,2)          
,(73229903,2,NULL,2)           
,(73229903,2,1,2);

SELECT number AS [@number]
      ,Type AS [@Type] 
      ,Trat AS [@Trat]
      ,Frecv AS [@Frecv]
FROM @Dummy 
FOR XML PATH('polita')

--The result

<polita number="31301879" Trat="1" Frecv="2" />
<polita number="73229903" Type="2" Frecv="2" />
<polita number="73229903" Type="2" Trat="1" Frecv="2" />

If you need NULLs instead of empty strings you can use NULLIF().

This answer might help you to understand how XML deals with empty vs. NULL

Upvotes: 1

Related Questions