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.
| Author |
Topic |
|
Humate
Posting Yak Master
101 Posts |
 Posted - 2012-01-06 : 08:26:15
|
| 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-06 : 08:39:32
|
| 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.aspxAlso, 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 |
 |
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2012-01-06 : 08:55:03
|
| 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 type2) Dynamic SQL/Xquery to retrieve all XML headers and generate standard looking data tableDoes 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. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-06 : 10:21:39
|
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)SCROSS APPLY s.ColNames.nodes('./col') T(c) |
|
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2012-01-06 : 12:46:44
|
| 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 |
|
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2012-01-10 : 09:41:31
|
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 BEGINDECLARE @Return NVARCHAR(MAX)SELECT @Return = SUBSTRING(@ShreadColumnText, CHARINDEX(@OpenHeader,@ShreadColumnText)+ LEN(@OpenHeader), CHARINDEX(@CloseHeader,@ShreadColumnText)- CHARINDEX(@OpenHeader,@ShreadColumnText)- LEN(@OpenHeader)) RETURN @Return ENDGOSELECT intPersonID, dbo.ShreadTextXMLHeaders('<FirstName>Bob</FirstName>', '<FirstName>', '</FirstName>') AS testshreadtypedXML,dbo.ShreadTextXMLHeaders([strApplicationXML], '<FirstName>', '</FirstName>') AS testshreadFROM tblCases |
|
|
|
|
|
|
|
|
XML data stored as text Conversion