Converting XML Column

Question

Post reply

Converting XML Column

kev43barrie

Right there with Babe

Points: 756
More actions
December 1, 2011 at 3:30 am

#247725

I am extracting data from tables to export into an Excel spreadsheet.
One of the columns has a data type of XML.
e.g. -
<ArrayOfFormItem xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FormItem>
<ControlId>Club_Licensed</ControlId>
<ItemValue>y</ItemValue>
<ItemText>Yes</ItemText>
</FormItem>
<FormItem>
<ControlId>Club_Type</ControlId>
<ItemValue>2</ItemValue>
<ItemText>Journal Club</ItemText>
</FormItem>
How can I extract the details to make it more readable?
Any ideas?
Kev.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

See if this helps

DECLARE @T TABLE(ID INT, XMLCol XML)

INSERT INTO @T(ID,XMLCol)

VALUES(1,

'<ArrayOfFormItem xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<FormItem>

<ControlId>Club_Licensed</ControlId>

<ItemValue>y</ItemValue>

<ItemText>Yes</ItemText>

</FormItem>

<FormItem>

<ControlId>Club_Type</ControlId>

<ItemValue>2</ItemValue>

<ItemText>Journal Club</ItemText>

</FormItem>

</ArrayOfFormItem>')

SELECT ID,

x.r.value('ControlId[1]','VARCHAR(10)') AS ControlId,

x.r.value('ItemValue[1]','VARCHAR(10)') AS ItemValue,

x.r.value('ItemText[1]','VARCHAR(10)') AS ItemText

FROM @T

CROSS APPLY XMLCol.nodes('/ArrayOfFormItem/FormItem') AS x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kev43barrie Right there with Babe Points: 756 More actions · Answer 2

Thanks for that. My one problem is that not all the FormItem nodes will have both ItemValue and ItemText, some only have ItemText.

e.g.

<FormItem>

<ControlId>Judgement</ControlId>

<ItemValue>4</ItemValue>

<ItemText>4</ItemText>

</FormItem>

<FormItem>

<ControlId>Comments</ControlId>

<ItemText>None.</ItemText>

</FormItem>

Kev

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

These will be returned as NULLs in the query. Have you tried it?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kev43barrie Right there with Babe Points: 756 More actions · Answer 4

Can this be done within a select statement with out creating a function?

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

kev43barrie (12/1/2011)
Can this be done within a select statement with out creating a function?

Very likely. You'll need to post more details though.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kev43barrie Right there with Babe Points: 756 More actions · Answer 6

This is my select Statement -

SELECT TicketAssessors.Name AS [Assessors Name], TicketAssessors.Designation AS [Assessors Designation],

TicketAssessors.RegistrationNumber AS [Assessors Reg (GMC) Number], TicketAssessors.Location AS [Assessors Location],

TicketAssessors.Email AS [Assesors Email], DefaultForms.Title AS [Form Title], FilledForms.FilledFormId, FilledForms.FormItems AS [Details Entered],

FilledForms.ContentLastModifiedDate AS [Content Last Modified Date], UserDetails.FullName AS [Content Last Modified By], UserDetails.GMC,

UserDetails.Department, UserDetails_1.FullName AS [Trainee Who Created Ticket], dbo.GetLocationDisplayText(Posts.LocationId) AS [Trainee Location]

FROM DefaultForms RIGHT OUTER JOIN

TicketForms INNER JOIN

Tickets ON TicketForms.TicketId = Tickets.TicketId INNER JOIN

TicketAssessors ON Tickets.TicketAssessorId = TicketAssessors.TicketAssessorId INNER JOIN

aspnet_Users INNER JOIN

UserDetails AS UserDetails_1 ON aspnet_Users.UserId = UserDetails_1.UserId ON Tickets.UserId = aspnet_Users.UserId ON

DefaultForms.DefaultFormId = TicketForms.DefaultFormId FULL OUTER JOIN

Posts ON TicketForms.PostId = Posts.PostId FULL OUTER JOIN

UserDetails INNER JOIN

aspnet_Users AS aspnet_Users_1 ON UserDetails.UserId = aspnet_Users_1.UserId INNER JOIN

FilledForms ON aspnet_Users_1.UserId = FilledForms.ContentLastModifiedById ON TicketForms.FilledFormId = FilledForms.FilledFormId

WHERE (TicketForms.PostId IN

(SELECT PostId

FROM Posts AS Posts_1

WHERE (LocationId IN

(SELECT LocationId

FROM dbo.GetAllUserLocations(NULL, '4AEA4215-F8AA-4513-A512-D1A13320B951', '86, 996, 992, 1012, 1016, 1020, 1024, 1064, 3667, 848, 3636')

AS GetAllUserLocations_1)) AND (StartDate < CONVERT(DATETIME, '07 DEC 2011', 106)) AND (EndDate > CONVERT(DATETIME,

'03 AUG 2011', 106)))) AND (FilledForms.ContentLastModifiedDate > CONVERT(DATETIME, '03 AUG 2011', 106)) AND

(FilledForms.ContentLastModifiedDate < CONVERT(DATETIME, '07 DEC 2011', 106))

ORDER BY FilledForms.FilledFormId

The column FilledForms.FormItems AS [Details Entered] is an xml column. I want to export the results into an Excel spread sheet and I need the xml column to be more readable for the customer.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

This will give you a new row for each XML FormItem element.

SELECT TicketAssessors.Name AS [Assessors Name], TicketAssessors.Designation AS [Assessors Designation],

TicketAssessors.RegistrationNumber AS [Assessors Reg (GMC) Number], TicketAssessors.Location AS [Assessors Location],

TicketAssessors.Email AS [Assesors Email], DefaultForms.Title AS [Form Title], FilledForms.FilledFormId,

--FilledForms.FormItems AS [Details Entered],

x.r.value('ControlId[1]','VARCHAR(10)') AS [Details Entered ControlId],

x.r.value('ItemValue[1]','VARCHAR(10)') AS [Details Entered ItemValue],

x.r.value('ItemText[1]','VARCHAR(10)') AS [Details Entered ItemText],

FilledForms.ContentLastModifiedDate AS [Content Last Modified Date], UserDetails.FullName AS [Content Last Modified By], UserDetails.GMC,

UserDetails.Department, UserDetails_1.FullName AS [Trainee Who Created Ticket], dbo.GetLocationDisplayText(Posts.LocationId) AS [Trainee Location]

FROM DefaultForms

RIGHT OUTER JOIN TicketForms

INNER JOIN Tickets ON TicketForms.TicketId = Tickets.TicketId

INNER JOIN TicketAssessors ON Tickets.TicketAssessorId = TicketAssessors.TicketAssessorId

INNER JOIN aspnet_Users

INNER JOIN UserDetails AS UserDetails_1 ON aspnet_Users.UserId = UserDetails_1.UserId ON Tickets.UserId = aspnet_Users.UserId ON DefaultForms.DefaultFormId = TicketForms.DefaultFormId

FULL OUTER JOIN Posts ON TicketForms.PostId = Posts.PostId

FULL OUTER JOIN UserDetails

INNER JOIN aspnet_Users AS aspnet_Users_1 ON UserDetails.UserId = aspnet_Users_1.UserId

INNER JOIN FilledForms ON aspnet_Users_1.UserId = FilledForms.ContentLastModifiedById ON TicketForms.FilledFormId = FilledForms.FilledFormId

CROSS APPLY FilledForms.FilledFormId.nodes('/ArrayOfFormItem/FormItem') AS x(r)

WHERE (TicketForms.PostId IN

(SELECT PostId

FROM Posts AS Posts_1

WHERE (LocationId IN

(SELECT LocationId

FROM dbo.GetAllUserLocations(NULL, '4AEA4215-F8AA-4513-A512-D1A13320B951', '86, 996, 992, 1012, 1016, 1020, 1024, 1064, 3667, 848, 3636')

AS GetAllUserLocations_1)) AND (StartDate < CONVERT(DATETIME, '07 DEC 2011', 106)) AND (EndDate > CONVERT(DATETIME,

'03 AUG 2011', 106)))) AND (FilledForms.ContentLastModifiedDate > CONVERT(DATETIME, '03 AUG 2011', 106)) AND

(FilledForms.ContentLastModifiedDate < CONVERT(DATETIME, '07 DEC 2011', 106))

ORDER BY FilledForms.FilledFormId

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kev43barrie Right there with Babe Points: 756 More actions · Answer 8

Ended up creating a function and calling the function in my select statement. Wasn't wanting to do that, but seems to be the best way.

func -

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N'[dbo].[fnXMLReadable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION dbo.fnXMLReadable

GO

CREATE FUNCTION dbo.fnXMLReadable

(

@FormDetails XML

)

RETURNS varchar(MAX)

AS

BEGIN

DECLARE @result varchar(MAX)

DECLARE @T TABLE(ID INT, XMLCol XML)

INSERT INTO @T(ID,XMLCol)VALUES(1,@FormDetails)

SELECT @result = COALESCE(@result + '] ', '') + '[' + (ISNULL(RIGHT(x.r.value('ControlId[1]','VARCHAR(MAX)'), LEN(x.r.value('ControlId[1]','VARCHAR(MAX)')) - 4), '') + ' : Value - ' + ISNULL(x.r.value('ItemValue[1]','VARCHAR(MAX)'), '') + ': Text - ' + ISNULL(x.r.value('ItemText[1]','VARCHAR(MAX)'), ''))

FROM @T

CROSS APPLY XMLCol.nodes('/ArrayOfFormItem/FormItem') AS x(r)

RETURN @Result

END

GO

Thanks for your help.

drew.allen SSC Guru Points: 76989 More actions · Answer 9

kev43barrie (12/1/2011)
Ended up creating a function and calling the function in my select statement. Wasn't wanting to do that, but seems to be the best way.

Then why create one? There's nothing forcing you to use a function. If you're worried about reusability, I would think that a VIEW would be a much better approach in this situation, because it would likely perform better.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA