Manipulate XMLNamespaces

Question

I have the following output from SQL using FOR XML clause:

<q17:DestinationSection xmlns:q17="http://ITrack.Transmission/2011/02/25/Objects">
<q17:DestinationCode>1</q17:DestinationCode>
<q17:DestinationName>Strada Rampei 9, Iasi</q17:DestinationName>
<q17:DestinationAddress1>Strada Rampei 9, Iasi</q17:DestinationAddress1>
<q17:DestinationAddress2>
xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
</q17:DestinationAddress2>
</q17:DestinationSection>

The DestinationSection is the main root for this block of data. Is there any possibility to do some workaround and to have something like below in the <q17:DestinationAddress2></q17:DestinationAddress2> tag?

<q17:DestinationAddress2 xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"</q17:DestinationAddress2>

I have tried something but I get an error that says that I need to declare the namespace but I really don't know where to "introduce" that definition.

My SQL statement

DECLARE @XMLFINAL VARCHAR(MAX)
SET @XMLFINAL=''
DECLARE @XMLFINAL2 VARCHAR(MAX)
SET @XMLFINAL2=''
DECLARE @NUMBER NVARCHAR(100)
DECLARE @NUMBER2 NVARCHAR(100)
DECLARE @XML VARCHAR(MAX)
DECLARE @XML2 VARCHAR(MAX)

DECLARE Rec CURSOR FAST_FORWARD FOR

SELECT  GID FROM PurchaseDocumentsHeader

OPEN Rec
FETCH NEXT FROM Rec INTO @NUMBER2
WHILE @@FETCH_STATUS = 0 
BEGIN
SET @XML2=''    


;WITH XMLNAMESPACES ('http://ITrack.Transmission/2011/02/25/Objects' as q17)


SELECT @XML2= (

SELECT 
DestCode AS 'q17:DestinationCode', DestDescr 'q17:DestinationName', DestAddr AS 'q17:DestinationAddress1', DestAddr2 AS 'q17:DestinationAddress2',
DestZIP AS 'q17:DestinationZIP'

FROM PurchaseDocumentsHeader WHERE GID=@NUMBER2
FOR XML RAW('q17:DestinationSection'),ELEMENTS
)

FETCH NEXT FROM Rec INTO @NUMBER2

SET @XMLFINAL2=@XMLFINAL2+@XML2
END

CLOSE Rec DEALLOCATE Rec

EDIT

Please find below my DDL. It's a view used to extract the data from an official table.

CREATE VIEW [dbo].[PurchaseDocumentsHeader]
AS
SELECT esd.GID, esd.ADRegistrationDate, esgo.Code AS DestCode, esgo.Description AS DestDescr, esgo.Address1 AS DestAddr, esgo.fCityCode AS DestCity,
 esgp.TaxRegistrationNumber AS DestZIP, 'xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' AS DestAddr2, esgo.Description AS DestRomanized,
 esgo.Address1 AS DestAddress1Romanized, 
 'xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' AS DestAddress2Romanized,  esgp.TaxRegistrationNumber AS DestZIPRom,  
 esgo.fCityCode AS DestCityRomanized, esgo.fCountryCode AS DestCountry, cast('DestinationGLN xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>' AS XML) AS DestGLN, 
 'xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' AS DestCoord
FROM ESFIDocumentTrade esd
LEFT JOIN ESGOSites esgo on esd.fDeliverySiteGID=esgo.GID
LEFT JOIN ESFITradeAccount esc on esd.fTradeAccountGID=esc.GID
LEFT JOIN ESGOPerson esgp on esc.fPersonCodeGID=esgo.GID
LEFT JOIN ESFIDocumentType est on esd.fADDocumentTypeGID=est.GID
WHERE esd.fTransitionStepCode='APPROVED' AND est.Code='CVR'
AND YEAR(esd.ADRegistrationDate)=YEAR(GETDATE())
AND MONTH(esd.ADRegistrationDate)=MONTH(GETDATE())
AND DAY(esd.ADRegistrationDate)=DAY(GETDATE())

GO

Later edit

CREATE TABLE DocPurcharseHeader
(
ADRegistrationDate date,
Code NVARCHAR(4000),
Description NVARCHAR(4000),
Address1 NVARCHAR (4000),
Address2 NVARCHAR (4000),
City NVARCHAR (4000),
ZIPCode NVARCHAR(4000)
)

INSERT INTO DocPurcharseHeader (ADRegistrationDate, Code, Description, Address1, Address2, City, ZIPCode)
VALUES('2017-10-16', '01', 'MyPOS', 'MyPOSAddress1', 'MyPOSAddress2', 'BUCHAREST', '123456')

2nd Later edit

;WITH XMLNAMESPACES ('http://ITrack.Transmission/2011/02/25/Objects' as q17)

    SELECT @XMLSalesOrders=(
    SELECT DestCode AS [q17:DestinationCode]
          ,DestDescr AS [q17:DestinationName]
          ,DestAddr AS [q17:DestinationAddress1]
          ,DestAddr2 AS [q17:DestinationAddress2]
    FROM PurchaseDocumentsHeader
    FOR XML PATH('q17:DestinationSection'),ELEMENTS XSINIL,ROOT('q17:DestinationSections'))

This code above is generating the below output, without XSINL directive:

<q17:DestinationSections xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q17="http://ITrack.Transmission/2011/02/25/Objects">
<q17:DestinationSection>
<q17:DestinationCode>1</q17:DestinationCode>
<q17:DestinationName>Strada Rampei 9, Iasi</q17:DestinationName>
<q17:DestinationAddress1>Strada Rampei 9, Iasi</q17:DestinationAddress1>
<q17:DestinationAddress2/>
</q17:DestinationSection>
<q17:DestinationSection>

Answer 1

you are trying to out-trick the very mighty XML engine.

As stated in your other question:

Never do this in a CURSOR! They are bad and evil, coming directly of the hell of procedural thinking and were invented by the devil of spaghetti code...

Try it like this:

I use your table DDL an insert some data. The second row will have a NULL in address2. Normally XML will simply omit NULL values. A not existing node is read as a NULL value. But you can force NULLs to be introduced as xsi.nil="true" with ELEMENTS XSINIL:

CREATE TABLE DocPurcharseHeader
(
ADRegistrationDate date,
Code NVARCHAR(4000),
Description NVARCHAR(4000),
Address1 NVARCHAR (4000),
Address2 NVARCHAR (4000),
City NVARCHAR (4000),
ZIPCode NVARCHAR(4000)  
);

INSERT INTO DocPurcharseHeader (ADRegistrationDate, Code, Description, Address1, Address2, City, ZIPCode)
VALUES('2017-10-16', '01', 'MyPOS', 'MyPOSAddress1', 'MyPOSAddress2', 'BUCHAREST', '123456')
     ,('2017-10-16', '01', 'MyPOS', 'MyPOSAddress1', NULL, 'BUCHAREST', '123456');

WITH XMLNAMESPACES('http://ITrack.Transmission/2011/02/25/Objects' AS q17)
SELECT Code AS [q17:DestinationCode]
      ,Description AS [q17:DestinationName]
      ,Address1 AS [q17:DestinationAddress1]
      ,Address2 AS [q17:DestinationAddress2]
FROM DocPurcharseHeader
FOR XML PATH('q17:DestinationSection'),ELEMENTS XSINIL,ROOT('q17:DestinationSections');

The result

<q17:DestinationSections xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q17="http://ITrack.Transmission/2011/02/25/Objects">
  <q17:DestinationSection>
    <q17:DestinationCode>01</q17:DestinationCode>
    <q17:DestinationName>MyPOS</q17:DestinationName>
    <q17:DestinationAddress1>MyPOSAddress1</q17:DestinationAddress1>
    <q17:DestinationAddress2>MyPOSAddress2</q17:DestinationAddress2>
  </q17:DestinationSection>
  <q17:DestinationSection>
    <q17:DestinationCode>01</q17:DestinationCode>
    <q17:DestinationName>MyPOS</q17:DestinationName>
    <q17:DestinationAddress1>MyPOSAddress1</q17:DestinationAddress1>
    <q17:DestinationAddress2 xsi:nil="true" />
  </q17:DestinationSection>
</q17:DestinationSections>

UPDATE: About NULL values:

Try this

DECLARE @DummyTable TABLE(SomeDescription VARCHAR(500), SomeValue VARCHAR(100));
INSERT INTO @DummyTable VALUES('A real NULL value',NULL)
                             ,('An empty string','')
                             ,('A blank string','   ')
                             ,('Some Text','blah blah');

WITH XMLNAMESPACES('SomeURL' AS q17)
SELECT SomeDescription AS [q17:Description]
      ,SomeValue AS [q17:Value]
FROM @DummyTable                
FOR XML PATH('q17:row'),ELEMENTS XSINIL,ROOT('root');   

To get this

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q17="SomeURL">
  <q17:row>
    <q17:Description>A real NULL value</q17:Description>
    <q17:Value xsi:nil="true" />
  </q17:row>
  <q17:row>
    <q17:Description>An empty string</q17:Description>
    <q17:Value></q17:Value>
  </q17:row>
  <q17:row>
    <q17:Description>A blank string</q17:Description>
    <q17:Value>   </q17:Value>
  </q17:row>
  <q17:row>
    <q17:Description>Some Text</q17:Description>
    <q17:Value>blah blah</q17:Value>
  </q17:row>
</root>

You can see, that the real NULL is encoded as xsi:nil="true", while the empty string is shown as <q17:Value></q17:Value> (which is exactly the same as <q17:Value />).

Check this answer for some examples about NULL and empty. Check this answer to understand more about text()