Sherzod Muminov Sherzod Muminov - 4 months ago 10
SQL Question

for xml explicit dealing with namespace

I'm trying to query

explicit xml
. For that purposes I have to use
namespace
. Here is a simplified version:

SELECT 1 as Tag,
NULL as Parent,
i.SSN as [SSN!1]
FROM IRS_Table as i
for xml explicit


But I want xml to look like this:

<irs:SSN>999-99-9999</irs:SSN>


Here is declaration of namespace in the beginning tags:

xmlns:irs="urn:us:gov:treasury:irs:common"


How to revise script so that to see in output namespace?

EDIT

<?xml version="1.0" encoding="utf-8"?>
<n1:Form109495CTransmittalUpstream xmlns:irs="urn:us:gov:treasury:irs:common">
<irs:SSN>999-99-9999</irs:SSN>
</n1:Form109495CTransmittalUpstream>

Answer

You can use following query to include namespace into for xml explicitoutput.

SELECT 1                   as Tag,  
       NULL                as Parent,  
       i.SSN               as [irs:SSN!1],
       'urn:us:gov:treasury:irs:common' as [irs:SSN!1!xmlns:irs] --namespace is here
FROM   IRS_Table as i
for xml  explicit