CODEWITHSUNDEEP
sql-serverxml
Gerry
Gerry

Reputation: 11

how to generate xml with element and attribute using xml explicit; root with xmlns

i need xml like this:

`<root xmlns="http://www.sample.com"
 <lkz>12345</lkz>
   <styles>
     <style>
     </style>
     <style>
     </style>
  </styles>
</root>`

i found this:

how-to-generate-xml-with-element-and-attribute-using-xml-explicit

perfect for my xml problem, but i need on

<root> = <root xmlns="http://www.sample.com"

i must need xml explicit !!!! ( because cdata )

thanks

Upvotes: 0

Views: 92

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Are you aware of the fact, that CDATA is outdated? You might read this (plus the links at the end).

You can solve this like here:

DECLARE @dummy TABLE(style VARCHAR(100));
INSERT INTO @dummy VALUES
 ('Some red style')
,('Another blue style');

--This query creates what you asked for (at least I hope so)

SELECT 1 AS Tag
      ,NULL AS Parent
      ,'http://www.sample.com' AS [root!1!xmlns] 
      ,12345 AS [root!1!lkz!element]
      ,NULL AS [styles!2]
      ,NULL AS [style!3!!cdata]

UNION 
SELECT 2
      ,1
      ,NULL
      ,NULL
      ,NULL
      ,NULL
FROM @dummy

UNION 
SELECT 3
      ,2
      ,NULL
      ,NULL
      ,NULL
      ,style
FROM @dummy

FOR XML EXPLICIT

The result

<root xmlns="http://www.sample.com">
  <lkz>12345</lkz>
  <styles>
    <style><![CDATA[Another blue style]]></style>
    <style><![CDATA[Some red style]]></style>
  </styles>
</root>

--This is the way you should approach this issue

WITH XMLNAMESPACES(DEFAULT 'http://www.sample.com')
SELECT 12345 AS lkz
      ,(
        SELECT style
        FROM @dummy 
        FOR XML PATH(''),ROOT('styles'),TYPE
       )
FOR XML PATH('root');

The result

<root xmlns="http://www.sample.com">
  <lkz>12345</lkz>
  <styles xmlns="http://www.sample.com">
    <style>Some red style</style>
    <style>Another blue style</style>
  </styles>
</root>

Only problem here is: the repeated namespace due to the sub-select. This is not wrong, but annoying. A well known issue for years (Please vote for the related connect article).

Upvotes: 1

Related Questions