0

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

tolanj
  • 3,651
  • 16
  • 30
Gerry
  • 11
  • 4

1 Answers1

1

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).

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • GREAT !! THANKS !! – Gerry Nov 06 '17 at 16:04
  • @Gerry, As you are new here (Btw: Welcome!), please allow me one hint: It is very kind of you to say *thanks* you, but it would be even kinder, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! – Shnugo Nov 06 '17 at 20:37