problem with Cross Apply For XML

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 2008 Forums

Transact-SQL (2008)

problem with Cross Apply For XML

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-12 : 14:22:12

When using Cross Apply with FOR XML, i'm able to concatenate rows, but when the value in the row is <U3, for example, it concatenates the rows with < and it doesnt look right. Below are the two sample tables and the query I was using to show how this is occuring. Is there anyway to fix this?

-- CREATE TABLES
CREATE TABLE #orders
(
ORDERID INT,
ORDERNOTES VARCHAR(30)

)

CREATE TABLE #ORDERLINES
(
LINEITEMID INT,
ORDERID INT,
LINENOTES VARCHAR(3)

)

--INSERT INTO TABLES
INSERT INTO #ORDERLINES VALUES(50,1,'<U3')

INSERT INTO #ORDERLINES VALUES(51,1,'<U4')

INSERT INTO #ORDERLINES VALUES(52,2,'K92')
INSERT INTO #ORDERLINES VALUES(53,2,'K23')
INSERT INTO #ORDERLINES VALUES(50,2,'')

--QUERY

SELECT *
FROM #orders AS o
CROSS APPLY (
SELECT linenotes + ','
FROM #orderLines AS ol
WHERE ol.orderID = o.orderID
FOR XML PATH('') )
temp ( listOfLineNotes )

DROP TABLE #ORDERS
DROP TABLE #ORDERLINES

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-12 : 14:23:31

its weird, you'll have to run that code above to see what i'm referring to, it puts a special character where the "<" is

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-12 : 14:34:41

actually, it might more clear if you use this code instead:

SELECT * FROM #orderlines ol
CROSS APPLY (SELECT opp.linenotes + ',' FROM #orderlines AS opp WHERE ol.orderid = opp.orderid FOR XML PATH(''))
temp (listofnotes)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-12 : 14:41:08

The < and > characters are used in XML to identify elements. A string that contains them will escape them to < and > respectively when converting to XML.

You can use the REPLACE() function to replace < and > with < and >:

SELECT ol.*, REPLACE(REPLACE(listofnotes,'&lt;','<'),'&gt;','>') FROM #orderlines ol
CROSS APPLY (SELECT opp.linenotes + ',' FROM #orderlines AS opp WHERE ol.orderid = opp.orderid FOR XML PATH(''))
temp (listofnotes)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 14:44:38

Using Joe8079's example, you can wrap the result into a single XML element and run the value function against it which prevents the tokenization.

SELECT *
FROM   #orderlines ol
    CROSS APPLY (SELECT (
    SELECT opp.linenotes + ','
    FROM   #orderlines AS opp
    WHERE  ol.orderid = opp.orderid FOR XML PATH(''),ROOT('ABCD'),TYPE).value('/ABCD[1]','VARCHAR(8000)')
)
temp(listofnotes)

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-12 : 15:14:36

interesting, i'm going to give that a shot. i've never seen this before. Thanks for your help Rob and Sunitabeck!

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-12 : 15:42:29

both methods work in my report. thank you both for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources