XML name space prefix 'json' declaration is missing for FOR XML column name 'Value/@json:ValueType'

Question

I am working on a project where the stored procedure I am writing is producing XML format output. I want to add a namespace to the 'Main' node and add JSON ValueType to the sub nodes. Below is my code:

--- Fake tables in stored procedure
create table #Cdetails(cid int, cidtype varchar(5), name varchar(5), age int)

insert into #Cdetails
values (1001, 'N', 'John', 12), (1002, 'U', 'Rick', 19),
       (1003, 'N', 'Diane', 25), (1004, 'N', 'Kippy', 26)

--Output of stored procedure 

create table #final(cid int, cidtype varchar(5), xml_data xml)

insert into #final
    select
        cid = c1.cid,
        cidtype = c1.cidtype,
        case 
           when c1.cidtype = 'N' 
              then (select 
                        'Value/@json:ValueType'='Number',
                        ID = cd1.cid,
                        details = (select  
                                       cd1.name, cd1.age
                                   for xml path(''), type)
                    from  
                        #Cdetails cd1
                    where 
                        cd1.cid = c1.cid
                    for xml path('Main'))
           when c1.cidtype = 'N' 
              then (select 
                        ID = cd1.cid,
                        details = 'Null'            
                    from 
                        #Cdetails cd1
                    where 
                        cd1.cid = c1.cid
                    for xml path('Main')) 
        end as 'xml_data'
    from 
        #Cdetails c1 

;WITH XMLNAMESPACES ('http://www.integround.com/json' as json)  
SELECT * 
FROM #final 
WHERE xml_data IS NOT NULL
FOR XML PATH('Main')

drop table #Cdetails, #final

In this procedure, the XML is generated based on CIDType condition. So my current output is:

I want to add the namespace to the 'Main' node and add the JSON property to sub nodes.

Error

XML name space prefix 'json' declaration is missing for FOR XML column name 'Value/@json:ValueType'.

Desired output:

Any help?!

Andrei Odegov Andrei Odegov · Accepted Answer · 2018-07-04T15:40:59

Can the XML Construction help you?

DECLARE @cdetails table(
  cid INT, cidtype VARCHAR(5), name VARCHAR(5), age INT
);

INSERT INTO @cdetails VALUES
  (1001, 'N', 'John', 22), (1002, 'U', 'Rick', 21),
  (1003, 'N', 'Diane', 25), (1004, 'N', 'Kippy', 26);

SELECT
  cid, cidtype,
  CAST('' AS XML).query('
<Main xmlns:json="http://www.samplenamespace.com/json">
  <ID json:ValueType="Number">{sql:column("cid")}</ID>
  {
    if( sql:column("cidtype")="N" ) then
      <details>
        <name>{sql:column("name")}</name>
        <age>{sql:column("age")}</age>
      </details>
    else <details>Null</details>
  }
</Main>
  ')
FROM @cdetails;

XML name space prefix 'json' declaration is missing for FOR XML column name 'Value/@json:ValueType'

1 Answers