Search code examples
sql-serverxmlxml-attributefor-xml

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

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

Solution

  • 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>