Separating field into multiple columns XML/Varchar2/SQL

Question

I am working with a database that pulls data in from an application. The data appears as XML in the field, but the datatype is actually varchar2. How do I split the data into columns by the names in the brackets? So end result would be each name in <> would be the column header and the value between the closing and ending brackets would be the row data.

Example:

Column Name: VAL_TXT

xml:<objects.CollegeInfo>
  <collegeOrgId>0000000000</collegeOrgId>
  <useCollegeOrgId>0000000000</useCollegeOrgId>
  <collegeName>University [City, ST, USA]</collegeName>
  <collegeCountry>USA</collegeCountry>
  <collegeState>ST</collegeState>
  <fromDate>08/15/2004</fromDate>
  <toDate>05/15/2007</toDate>
  <degreeDate>08/15/2008</degreeDate>
  <gpaType>4PT</gpaType>
  <gradePointAverage>3.5</gradePointAverage>
  <enteredDegree>Bachelor&apos;s</enteredDegree>
</objects.CollegeInfo>

Each row may have different fields in them as well.

Also, this data is stored in an Oracle server, but I am pulling the data in through linked server on MS SQL.

Thanks!

EDIT: I did try the following

SELECT CAST(a.VAL_TXT as xml).value('(/objects.CollegeInfo/collegeOrgId)[1]','INT') AS col1
from TABLE a;

I received the error: Msg 9403, Level 16, State 1, Line 1 XML parsing: line 0, character 0, unrecognized input signature

New EDIT (Second try):

SELECT *
FROM
    (
       SELECT
          VAL_TXT = CAST(VAL_TXT AS XML)
       FROM
          TABLE
    ) t
    CROSS APPLY
    (
       SELECT
          collegeOrgId = x.n.value('collegeOrgId[1]','varchar(20)')
          ,useCollegeOrgId = x.n.value('useCollegeOrgId[1]','varchar(50)')
          ,collegeName = x.n.value('collegeName[1]','VARCHAR(100)')
          ,collegeCountry = x.n.value('collegeCountry[1]','VARCHAR(20)')
          ,collegeState = x.n.value('collegeState[1]','CHAR(2)')
          ,fromDate = x.n.value('fromDate[1]','varchar(30)')
          ,toDate = x.n.value('toDate[1]','varchar(30)')
          ,degreeDate = x.n.value('degreeDate[1]','varchar(30)')
          ,gpaType = x.n.value('gpaType[1]','VARCHAR(20)')
          ,gradePointAverage = x.n.value('gradePointAverage[1]','DECIMAL(5,3)')
          ,enteredDegree = x.n.value('enteredDegree[1]','VARCHAR(20)')
       FROM
          t.VAL_TXT.nodes ('objects.CollegeInfo') as x(n)
    ) c;

Receives error: Msg 9411, Level 16, State 1, Line 8 XML parsing: line 1, character 10, semicolon expected

Answer 1

DECLARE @TableWithXMLAsVarchar AS TABLE (XMLasVarchar VARCHAR(MAX))
INSERT INTO @TableWithXMLAsVarchar VALUES ('<objects.CollegeInfo>
  <collegeOrgId>0000000000</collegeOrgId>
  <useCollegeOrgId>0000000000</useCollegeOrgId>
  <collegeName>University [City, ST, USA]</collegeName>
  <collegeCountry>USA</collegeCountry>
  <collegeState>ST</collegeState>
  <fromDate>08/15/2004</fromDate>
  <toDate>05/15/2007</toDate>
  <degreeDate>08/15/2008</degreeDate>
  <gpaType>4PT</gpaType>
  <gradePointAverage>3.5</gradePointAverage>
  <enteredDegree>Bachelor&apos;s</enteredDegree>
</objects.CollegeInfo>')


SELECT *
FROM
    (
       SELECT
          XMLColumn = CAST(XMLasVarchar AS XML)
       FROM
          @TableWithXMLAsVarchar
    ) t
    CROSS APPLY
    (
       SELECT
          collegeOrgId = x.n.value('collegeOrgId[1]','INT')
          ,useCollegeOrgId = x.n.value('useCollegeOrgId[1]','INT')
          ,collegeName = x.n.value('collegeName[1]','VARCHAR(100)')
          ,collegeCountry = x.n.value('collegeCountry[1]','VARCHAR(20)')
          ,collegeState = x.n.value('collegeState[1]','CHAR(2)')
          ,fromDate = x.n.value('fromDate[1]','DATE')
          ,toDate = x.n.value('toDate[1]','DATE')
          ,degreeDate = x.n.value('degreeDate[1]','DATE')
          ,gpaType = x.n.value('gpaType[1]','VARCHAR(20)')
          ,gradePointAverage = x.n.value('gradePointAverage[1]','DECIMAL(5,3)')
          ,enteredDegree = x.n.value('enteredDegree[1]','VARCHAR(20)')
       FROM
          t.XMLColumn.nodes ('objects.CollegeInfo') as x(n)
    ) c

Basically you kind of had the right idea but instead of selecting the value directly you have to select the value from the nodes. But because this data is in a table you actually have a little more to do. First you will have to cast the column as XML but you can't do that in the FROM statement so you can do it via a Common Table Expression [CTE] or an aliased Derived Table. After that you can CROSS/OUTER APPLY to get all of the columns.