November 24, 2009 at 3:48 pm
Hi guys, thanks for your time reading this .
at the moment i m just trying to find a way to produce something similar to this by using "for xml"
<TEST>
<TESTING>
<comment>acc</comment>
<number>1</number>
<boo>1</boo>
<INNERNODE>
<exe><![CDATA[aacc]]></exe>
</INNERNODE>
</TESTING>
</TEST>
and i have tried to use a for xml to do this by nested a for xml within the query
select
C_Comment as [comment],
i_number as [number],
boolean as [boo]
, (
SELECT
1 as [tag]
,0 as [parent]
,C_Comment as [comment!1!!exe]
FROM
TEST_FOR_XML
FOR XML EXPLICIT
) AS [INNERNODE]
FROM
TEST_FOR_XML
FOR XML PATH('TESTING'), ROOT('TEST')
and within the nested node,
it converted the xml tags < and > into the < and &bt;
am i going on the wrong track or i m missing the syntax ...
many thanks for all the help guys
November 26, 2009 at 8:55 pm
Can you provide your source data table structure TEST_FOR_XML and some test data in a readily consumable form, i.e. as insert statements.
Are you sure you need the CDATA section? If you were to give up that requirement, I believe you you generate your XML fairly easily with the FOR XML PATH.
If you really need the CDATA section, you are forced into using the ugly FOR XML EXPLICIT syntax.
Since FOR XML will automatically encode text content characters such as < > and & as < > and & respectively, a CDATA section shouldn't be necessary.
November 26, 2009 at 9:16 pm
OK, I've made the assumption that your table structure is something like the following:
CREATE TABLE #TEST_FOR_XML (
id int PRIMARY KEY,
C_Comment varchar(100),
i_number int,
boolean bit
)
Here are some test data, including some characters that need XML entity escaping.
INSERT INTO #TEST_FOR_XML (id, C_Comment, i_number, boolean)
SELECT 10, 'acc', 1, 1 UNION ALL
SELECT 11, 'BLAH', 17, 0 UNION ALL
SELECT 12, '<''"&>', 112, 1
This method uses FOR XML PATH. It cannot be made to generate a CDATA section.
SELECT
TOUT.C_Comment as [comment],
TOUT.i_number as [number],
TOUT.boolean as [boo],
(
SELECT TIN.C_Comment AS [exe]
FROM #TEST_FOR_XML TIN
WHERE (TIN.id = TOUT.id)
FOR XML PATH('INNERNODE'), TYPE
)
FROM #TEST_FOR_XML TOUT
FOR XML PATH('TESTING'), ROOT('TEST')
Here are the results:
<TEST>
<TESTING>
<comment>acc</comment>
<number>1</number>
<boo>1</boo>
<INNERNODE>
<exe>acc</exe>
</INNERNODE>
</TESTING>
<TESTING>
<comment>BLAH</comment>
<number>17</number>
<boo>0</boo>
<INNERNODE>
<exe>BLAH</exe>
</INNERNODE>
</TESTING>
<TESTING>
<comment><'"&></comment>
<number>112</number>
<boo>1</boo>
<INNERNODE>
<exe><'"&></exe>
</INNERNODE>
</TESTING>
</TEST>
This method uses FOR XML EXPLICIT, and uses a CDATA section. The FOR XML EXPLICIT syntax is definitely not intuitive, and you have to be careful to order the unioned rows correctly to avoid child elements ending up within the wrong parent elements.
SELECT
1 AS Tag,
0 AS Parent,
id AS [TESTING!1!id!hide],
C_Comment as [TESTING!1!comment!element],
i_number as [TESTING!1!number!element],
boolean as [TESTING!1!boo!element],
NULL AS [INTERNODE!2!exe!cdata]
FROM #TEST_FOR_XML
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
id,
NULL,
NULL,
NULL,
C_Comment
FROM #TEST_FOR_XML
ORDER BY [TESTING!1!id!hide], Tag
FOR XML EXPLICIT, ROOT('TEST')
Here are the results:
<TEST>
<TESTING>
<comment>acc</comment>
<number>1</number>
<boo>1</boo>
<INTERNODE>
<exe><![CDATA[acc]]></exe>
</INTERNODE>
</TESTING>
<TESTING>
<comment>BLAH</comment>
<number>17</number>
<boo>0</boo>
<INTERNODE>
<exe><![CDATA[BLAH]]></exe>
</INTERNODE>
</TESTING>
<TESTING>
<comment><'"&></comment>
<number>112</number>
<boo>1</boo>
<INTERNODE>
<exe><![CDATA[<'"&>]]></exe>
</INTERNODE>
</TESTING>
</TEST>
December 1, 2009 at 3:17 pm
hey man , really thanks for the reply . as i m a newbie , sorry to ask silly questions 🙂
can have a nested a for xml (path mode) with a for xml explicit mode 🙂
cheers man
November 22, 2010 at 9:30 am
Q from another noobie,
is there a way to escape the '<' and '>' so that it doesn't get converted to > and <?
I am using FOR XML PATH to combine comment fields. It is working great but some of the comments have portions framed by < and >. I would like to not have to do a replace after combining step.
Thanks in advance for your help. Just point me at an example and I can figure it out.
Jim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply