convert "FOR XML EXPLICIT" to Varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert "FOR XML EXPLICIT" to Varchar

Author

Topic

motsi
Starting Member

4 Posts

Posted - 2011-03-07 : 04:27:25

Hi,

I have a big problem. I have builded a XML with EXPLICIT form, but now, I want to convert into varchar or string, because I want to show the XML construction with PHP code. I don't know any other possibility to catch the XML data with PHP Code. Do you know any other?

The SQL code is that:

(SELECT 1 As Tag,
	   NULL AS Parent,
	   '0' AS [rows!1!id],
	   NULL AS [row!2!id],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!cdata], 
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!cdata] 	
)
UNION ALL
(SELECT	2 AS Tag,
		1 AS Parent,
		NULL,
		ROW_NUMBER() OVER (ORDER BY t.test_id),
		t.test_aplicacion,
		r2.req_name,
		r1.req_name,
		tst.testset_name,
		t.test_name,
		t.test_criticidad,
		stp.step_description,
		step_expectet_result,
		CASE WHEN (qtr.step_actual_result is null) THEN '' ELSE qtr.step_actual_result END,
		CASE 
			WHEN (qtr.step_run_status is null) THEN '<b style="color:grey">No Run</b>' 
			WHEN (qtr.step_run_status = 'Passed') THEN '<b style="color:green">Passed</b>' 
			WHEN (qtr.step_run_status = 'Failed') THEN '<b style="color:red">Failed</b>' 
			WHEN (qtr.step_run_status = 'Pending') THEN '<b style="color:orange">Pending</b>' 
			WHEN (qtr.step_run_status = 'No Run') THEN '<b style="color:grey">No Run</b>' 
			WHEN (qtr.step_run_status = 'Not Completed') THEN '<b style="color:purple">Not Completed</b>' 
			WHEN (qtr.step_run_status = 'N/A') THEN '<b style="color:brown">N/A</b>' 
			ELSE '<b style="color:black" >' + qtr.step_run_status + '</a>' END,
		CASE WHEN (qtr.test_run_date is null) THEN '' ELSE CONVERT(varchar, qtr.test_run_date, 121) END,
		'<a style="cursor:hand;" onCiclick="createWindow('+ char(39) + CONVERT(varchar, t.test_id) + char(39) +', ' + char(39) + t.test_name + char(39) + ');" >' + CONVERT(varchar, tidc.total) + '</a>'
	FROM    dbo.qc_tests_instances AS ti
				INNER JOIN dbo.qc_testsets AS tst ON ti.testset_id = tst.testset_id AND ti.project_id = tst.project_id 
				INNER JOIN dbo.qc_tests AS t ON ti.test_id= t.test_id AND t.project_id= ti.project_id 
				INNER JOIN dbo.qc_tests_steps AS stp ON stp.test_id= t.test_id and t.project_id = stp.project_id 
				INNER JOIN dbo.qc_requirements As r1 ON r1.req_id = ti.testinstance_requirement_coverage and r1.project_id=ti.project_id 
				INNER JOIN dbo.qc_requirements AS r2 ON r1.req_father_id= r2.req_id and r1.project_id = r2.project_id 
				INNER JOIN dbo.qc_list_cycles_Cuadrificador AS cq ON cq.cycle_standard_name = ti.testinstance_ciclo
				LEFT JOIN  dbo.qc_quality_tests_runs AS qtr ON qtr.test_id =t.test_id AND qtr.step_id= stp.step_id AND ti.testinstance_id = qtr.testinstance_id AND ti.project_id= qtr.project_id
				INNER JOIN dbo.Vista_testinstance_defectsCount AS tidc ON tidc.test_id = t.test_id and tidc.project_id = t.project_id
	WHERE ((qtr.test_run_date IN
			  (SELECT MAX(qtr1.test_run_date) AS Expr1
				FROM  dbo.qc_quality_tests_runs AS qtr1                  
				WHERE (qtr1.test_id = t.test_id) AND (qtr1.step_id = stp.step_id) AND (qtr1.testinstance_id = ti.testinstance_id) AND (qtr.project_id= ti.project_id)))
				OR (qtr.qc_update_date is null)) AND test_dossier_id=77 AND cycle_id=1)
FOR XML EXPLICIT

You can try with that code that it doesn't need tables:

(SELECT 1 As Tag,
	   NULL AS Parent,
	   '0' AS [rows!1!id],
	   NULL AS [row!2!id],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!cdata], 
	   NULL AS [row!2!cell!element],
	   NULL AS [row!2!cell!cdata] 	
)
UNION ALL
(SELECT	2 AS Tag,
		1 AS Parent,
		NULL,
		'1',
		'Dossier',
		'Requerimient Name 1',
		'Requerimient Name 2',
		'TestSet Name',
		'Test Name',
		'Priority',
		'Step Description',
		'Step Expected Result',
		'Step Actual Result',
		CASE 
			WHEN (null is null) THEN '<b style="color:grey">No Run</b>' 
			WHEN (null = 'Passed') THEN '<b style="color:green">Passed</b>' 
			WHEN (null = 'Failed') THEN '<b style="color:red">Failed</b>' 
			WHEN (null = 'Pending') THEN '<b style="color:orange">Pending</b>' 
			WHEN (null = 'No Run') THEN '<b style="color:grey">No Run</b>' 
			WHEN (null = 'Not Completed') THEN '<b style="color:purple">Not Completed</b>' 
			WHEN (null = 'N/A') THEN '<b style="color:brown">N/A</b>' 
			ELSE '<b style="color:black" >' + null + '</a>' END,
		CASE WHEN ('2011-03-01' is null) THEN '' ELSE CONVERT(varchar, '2011-03-01', 121) END,
		'<a style="cursor:hand;" onCiclick="createWindow('+ char(39) + CONVERT(varchar, 345) + char(39) +', ' + char(39) + 'TEST NAME' + char(39) + ');" >1</a>')
FOR XML EXPLICIT

The result of the last one query is:

<rows id="0">
  <row id="1">
    <cell>Dossier</cell>
    <cell>Requerimient Name 1</cell>
    <cell>Requerimient Name 2</cell>
    <cell>TestSet Name</cell>
    <cell>Test Name</cell>
    <cell>Priority</cell>
    <cell>Step Description</cell>
    <cell>Step Expected Result</cell>
    <cell>Step Actual Result</cell>
    <cell><![CDATA[<b style="color:grey">No Run</b>]]></cell>
    <cell>2011-03-01</cell>
    <cell><![CDATA[<a style="cursor:hand;" onCiclick="createWindow('345', 'TEST NAME');" >1</a>]]></cell>
  </row>
</rows>

I tried to SELECT the query result another time with CONVERT(varchar, field), but it doesn't work.

I hope that somebody will help me, thank you very much for your atention.

see you,

_________________
MOTSI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 05:24:26

Use CONVERT(NVARCHAR(MAX), Field) instead.
XML is unicode.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 05:25:37

Also, CDATA elements must at most 128 characters, or you get this error message

Maximum length is 128.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 05:34:31

This is how your XML result looks like. You have to deal with special characters < > &

<rows id="0">
  <row id="1">
    <cell>Dossier</cell>
    <cell>Requerimient Name 1</cell>
    <cell>Requerimient Name 2</cell>
    <cell>TestSet Name</cell>
    <cell>Test Name</cell>
    <cell>Priority</cell>
    <cell>Step Description</cell>
    <cell>Step Expected Result</cell>
    <cell>Step Actual Result</cell>
    <cell><b style="color:grey">No Run</b></cell>
    <cell>2011-03-01</cell>
    <cell><a style="cursor:hand;" onCiclick="createWindow('345', 'TEST NAME');" >1</a></cell>
  </row>
</rows>

N 56°04'39.26"
E 12°55'05.63"

motsi
Starting Member

4 Posts

Posted - 2011-03-07 : 05:36:36

Hi Peso,

Thank you for your reply.
I applied the CONVERT(NVARCHAR(MAX), Field), but it returns the same error.

This is the code that I have know:

SELECT CONVERT(NVARCHAR(MAX), Field)
FROM (
	(SELECT 1 As Tag,
		   NULL AS Parent,
		   '0' AS [rows!1!id],
		   NULL AS [row!2!id],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!cdata], 
		   NULL AS [row!2!cell!element],
		   NULL AS [row!2!cell!cdata] 	
	)
	UNION ALL
	(SELECT	2 AS Tag,
			1 AS Parent,
			NULL,
			'1',
			'Dossier',
			'Requerimient Name 1',
			'Requerimient Name 2',
			'TestSet Name',
			'Test Name',
			'Priority',
			'Step Description',
			'Step Expected Result',
			'Step Actual Result',
			CASE 
				WHEN (null is null) THEN '<b style="color:grey">No Run</b>' 
				WHEN (null = 'Passed') THEN '<b style="color:green">Passed</b>' 
				WHEN (null = 'Failed') THEN '<b style="color:red">Failed</b>' 
				WHEN (null = 'Pending') THEN '<b style="color:orange">Pending</b>' 
				WHEN (null = 'No Run') THEN '<b style="color:grey">No Run</b>' 
				WHEN (null = 'Not Completed') THEN '<b style="color:purple">Not Completed</b>' 
				WHEN (null = 'N/A') THEN '<b style="color:brown">N/A</b>' 
				ELSE '<b style="color:black" >' + null + '</a>' END,
			CASE WHEN ('2011-03-01' is null) THEN '' ELSE CONVERT(varchar, '2011-03-01', 121) END,
			'<a style="cursor:hand;" onCiclick="createWindow('+ char(39) + CONVERT(varchar, 345) + char(39) +', ' + char(39) + 'TEST NAME' + char(39) + ');" >1</a>')
	FOR XML EXPLICIT
) Data(Field)

The strange error that always returns me is:

Mens. 156, Nivel 15, Estado 1, Línea 45
Incorrect syntax near the keyword 'FOR'.

see you,

_________________
MOTSI

motsi
Starting Member

4 Posts

Posted - 2011-03-07 : 10:57:10

Hi,

That issue, it's very urgent

, if anybody can help me, please reply the post.

see you soon,

_________________
MOTSI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 15:37:53

Why is there an ending paranthesis at the line just above FOR XML?

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 17:40:36

I don't think you can do the casting the way you are trying to do it. You have to reorganize your select-unions into an intermediate table, CTE, or subquery and then apply the xml explicit clause on the resulting table. See an example below. I have left out most of the columns, but it demonstrates the concept nonetheless.

with A as
(
select
   1 As Tag,
   NULL AS Parent,
   '0' AS col0,
   NULL AS col1,
   NULL AS col2,
   null as col3
union all
SELECT   
   2 AS Tag,
   1 AS Parent,
   NULL ,
   '1' ,
   'Dossier' ,
   'Requerimient Name 1'
)
select 
cast
(
   (
   select
      Tag,
      Parent,
      col0 as [rows!1!id],
      col1 [row!2!id],
      col2 [row!2!cell!element],
      col3 [row!2!cell!element]
   from
   A
   for xml explicit
   ) 
as varchar(max));

I want to add some unsolicited advice :--) You may want to you use xml path instead of xml explicit, at least for future projects. I am saying this for two reasons:
a) xml explicit is depracated.
b) xml path is soooo much simpler. Every time I have to look at or work with xml explict, I cringe, but xml path is a breeze; xml path requires none of this "bang" notation, the dummy unions or any of the other artifacts of xml explicit. And, it can do everything xml explicit can do.

I want to say that it may even be worthwhile to rewrite your current query using xml path even though you have invested time and efforts in it, but I won't say it :--)

motsi
Starting Member

4 Posts

Posted - 2011-03-14 : 04:07:30

Thank you vary much to everybody.
The last post helped me a lot.

See you,

_________________
MOTSI

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources