将字段分成多列 XML/Varchar2/SQL

Question

我正在使用一个从应用程序中提取数据的数据库。数据在字段中显示为 XML，但数据类型实际上是 varchar2。如何按括号中的名称将数据拆分为列？因此，最终结果将是<>中的每个名称将是列标题，右括号和结束括号之间的值将是行数据。

例：

列名称：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>

每行中也可能有不同的字段。

此外，这些数据存储在 Oracle 服务器中，但我通过 MS SQL 上的链接服务器将数据拉入。

谢谢！

编辑：我确实尝试了以下方法

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

我收到错误：Msg 9403，级别 16，状态 1，第 1 行XML 解析：第 0 行，字符 0，无法识别的输入签名

新编辑(第二次尝试(：

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;

收到错误：Msg 9411，级别 16，状态 1，第 8 行XML 分析：第 1 行，字符 10，应使用分号

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

基本上你的想法是正确的，但不是直接选择值，而是必须从节点中选择值。 但是，由于此数据位于表中，因此您实际上还有更多工作要做。 首先，您必须将列转换为 XML，但不能在 FROM 语句中执行此操作，因此您可以通过 Common Table Expression [CTE] 或别名Derived Table执行此操作。 之后，您可以CROSS/OUTER APPLY获取所有列。