DECLARE @X xml
SET @X='<classifications totalrecords="62"><classification numrecords="4">Dairy Farmers</classification><classification numrecords="6">Take Away Meal Outlets</classification><classification numrecords="52">Public Houses Activities Of</classification></classifications>'

[code]
SELECT t.u.value('.','varchar(100)') as desc,
t.u.value('./@numrecords','int') as count,
t.u.value('../@totalrecords,'int') as totalcount
FROM @X.nodes('/classifications/classification')t(u)

SELECT t.u.value('.','varchar(100)') as [desc],
t.u.value('./@numrecords','int') as count,
t.u.value('../@totalrecords','int') as totalcount
FROM YourTable YT
cross apply YT.YourXMLCol.nodes('/classifications/classification')t(u)

....
FROM (SELECT CAST(YourStringColumn AS XML) AS xmlCol FROM YourTable) YT
cross apply xmlcol.nodes('/classifications/classification')t(u)

quote:

---

Originally posted by rcp

When I try to CAST the column to XML, I get the error message:

Msg 9421, Level 16, State 1, Line 1
XML parsing: line 1, character 77, illegal name character

I could change the data in the column so that it is comma delimited by using a replace. e.g.

<classifications totalrecords="62"><classification numrecords="4">Dairy Farmers</classification><classification numrecords="6">Take Away Meal Outlets</classification><classification numrecords="52">Public Houses Activities Of</classification></classifications>

52,Totals,4,Dairy Farmers,6,Take Away Meal Outlets,52,Public Houses Activities Of

Can I then split this? What do you think

Regards,

Roland

---

quote:

---

Originally posted by rcp

I have been informed that running a loop query would give me what I want. In the table there is a unique icdlog_guid. I could then use the original suggestion from visakh16 and loop through the icdlog_guid. What do you think?

---

---- TEST DATA ---------------------------------------
CREATE TABLE #tmp (id int, col1 VARCHAR(8000));
INSERT INTO #tmp VALUES (1, '210,Totals,210,Take Away Meal Outlets,10,Curry')
INSERT INTO #tmp VALUES (2, '1,Totals,1,Beer Wine And Spirit (Retail),')
INSERT INTO #tmp VALUES (3, '10,Totals,1,Cafes And Snack Bars,4,Chocolate')

-----USING Splitter function ---------------------------
SELECT
	*
FROM
	#tmp
	CROSS APPLY (SELECT * FROM MASTER.dbo.DelimitedSplit8K(col1,',') ) s



------PIVOTING     --------------------------------------
;WITH cte AS
(
SELECT
	Id,ItemNumber,Item
FROM
	#tmp
	CROSS APPLY (SELECT * FROM MASTER.dbo.DelimitedSplit8K(col1,',') ) s
)
SELECT
	*
FROM
	cte
PIVOT
(MAX(Item) FOR ItemNumber IN ([1],[2],[3],[4],[5],[6]))P

---- cleanup
DROP TABLE #tmp;