SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML data stored as text Conversion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Humate
Posting Yak Master

101 Posts

Posted - 01/06/2012 :  08:26:15  Show Profile  Reply with Quote
Hi All,

I have to retrieve XML data from a column where it is stored as text. I ideally want to split the text into the various columns by XML header, to translate the XML into a series of columns with values (so I can deal with it like any other SQL server record.

Can anyone comment on the best approach for this? I think it will need to be dynamic in order to create whatever headings the XML may contain.

Thanks in advance


sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/06/2012 :  08:39:32  Show Profile  Reply with Quote
All of what you listed can be done, but hard to say what exactly you should do without some sample data. Take a look at Brett's blog for some guidance in posting http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also, this page might be useful. It is long, but it has some very good examples and links to other pages: http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx
Go to Top of Page

Humate
Posting Yak Master

101 Posts

Posted - 01/06/2012 :  08:55:03  Show Profile  Reply with Quote
Thanks for the response. I wanted to discuss the general approach, it doesn't need to my specific XML. I think this needs to be done in two phases:

1) convert text to XML data type
2) Dynamic SQL/Xquery to retrieve all XML headers and generate standard looking data table

Does that sound correct, or can I work directly with the text? I'm struggling to find examples on both stages i.e. conversion and generating the columns. I've used Xquery previously for known headers, but never for a dynamic list.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/06/2012 :  10:21:39  Show Profile  Reply with Quote
As for converting to XML data type, assuming that it is well-formed, you should be able to just cast it to XML data type.

For dynamically querying, I am guessing that you need to find the node names and the create the relational tables based on that. How you would that, of course, depends on the structure of the XML.

Not sure if this will help or not, but given below is an example where I am declaring a varchar string and extracting the node names from it.
DECLARE @x VARCHAR(MAX);
SET @x = '<root><a>aa</a><b>bb</b></root>';


SELECT
	c.value('.','varchar(10)')
FROM
(
	SELECT CAST(
			CAST( @x AS XML).query(
				'for $a in /root/*
				return <col>{fn:local-name($a)}</col>')
	 AS XML) AS ColNames
)S
CROSS APPLY s.ColNames.nodes('./col') T(c)

Edited by - sunitabeck on 01/06/2012 10:22:05
Go to Top of Page

Humate
Posting Yak Master

101 Posts

Posted - 01/06/2012 :  12:46:44  Show Profile  Reply with Quote
I see, yes that looks like what I'm after - just wanted to validate the approach was sound first. I have tried converting the text to XML using CAST, but there must be errors in the XML string, it doesn't convert past my third row. I'll check the XML structure
Go to Top of Page

Humate
Posting Yak Master

101 Posts

Posted - 01/10/2012 :  09:41:31  Show Profile  Reply with Quote
Hmm, it seems the XML is isn't going to convert easily. As a temporary fix I'm going to need to search the string and pick out data between the two open/close XML headers, for each node I'm interested in.

Is there a better/more efficient way than using the below UDF? It seems to work with my string, but not with the column name containing the XML.


ALTER FUNCTION [dbo].[ShreadTextXMLHeaders] (@ShreadColumnText NVARCHAR(50), @OpenHeader NVARCHAR(50), @CloseHeader NVARCHAR(50))
RETURNS NVARCHAR (50)
AS BEGIN
DECLARE @Return NVARCHAR(MAX)
SELECT @Return = SUBSTRING(@ShreadColumnText, CHARINDEX(@OpenHeader,@ShreadColumnText)+ LEN(@OpenHeader), 
       CHARINDEX(@CloseHeader,@ShreadColumnText)- CHARINDEX(@OpenHeader,@ShreadColumnText)- LEN(@OpenHeader)) 
              
RETURN @Return 
END
GO


SELECT intPersonID, dbo.ShreadTextXMLHeaders('<FirstName>Bob</FirstName>', '<FirstName>', '</FirstName>') AS testshreadtypedXML
,dbo.ShreadTextXMLHeaders([strApplicationXML], '<FirstName>', '</FirstName>') AS testshread

FROM tblCases

Edited by - Humate on 01/10/2012 09:42:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000