Remove error code for xml attribute -- MS SQL For xml (_x003D)

123 Views Asked by At

I have the following sql code to generate a block of xml.

SELECT top 1
'http://www.crsoftwareinc.com/xml/ns/titanium/common/v1_0' AS [xmlns],
(SELECT top 1
count(*)AS 'number-of-accounts',
--
-- TO DO
--
FOR XML PATH('payment-import-header'), TYPE),
FOR XML RAW ('consumer-payment-import-job');

The putout is this: enter image description here

How can I get rid of "_x003D" from the attribute name? If I insert "=" or "@" it will add another random value. I'm assuming these are error tags.

Thanks

1

There are 1 best solutions below

3
On BEST ANSWER

You add a default namespace using the WITH XMLNAMESPACES clause, with the DEFAULT keyword.

Example:

WITH XMLNAMESPACES(DEFAULT 'http://www.crsoftwareinc.com/xml/ns/titanium/common/v1_0')
SELECT 1
FOR XML PATH('consumer-payment-import-job');

With result

<consumer-payment-import-job xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/common/v1_0">1</consumer-payment-import-job>