1

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

a ridi
  • 125
  • 2
  • 13
  • Try this: https://learn.microsoft.com/en-us/sql/relational-databases/xml/add-namespaces-to-queries-with-with-xmlnamespaces – Alex Aug 28 '17 at 15:41

1 Answers1

1

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>
TT.
  • 15,774
  • 6
  • 47
  • 88
  • Thanks for the response, this will create an attribute for all elements, I only want it for consumer-payment-import-job. – a ridi Aug 28 '17 at 16:42
  • @aridi Sadly, there is no straightforward way to do this if you have multiple `FOR XML` clauses in your query. There is a [microsoft connect issue with a request to suppress adding the namespace to each element for multiple `FOR XML` clauses](https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements). If you could upvote it, maybe they'll make this possible in the future. – TT. Aug 28 '17 at 17:20
  • @aridi The workaround involves string functions btw, I'm sure you can figure this out yourself. – TT. Aug 28 '17 at 17:21