0

I am having some issues pulling data I need from a database running SQL Server 2008 R2. The column that I am trying to pull data out of is in XML and I am having trouble parsing it. I tried looking up examples online of how to do this, but I seem to be struggling. In fact, some of the functions I saw do not seem to work in my queries, like the .nodes() or .value() function. Plus, the examples I see being used are different then how my data is stored. Here is an example of one of the XML records:

<?xml version="1.0" encoding="utf-8"?>
<view name="Terminate" className="Employee" viewName="Employee" dataArea="lmghr" executor="REMOTE_GRID" layoutType="1" isDisplayOnly="false">
    <action name="Terminate" type="UPDATE" isRequestAction="true" scheduleType="FOREGROUND" scheduleChoice="FOREGROUND"></action>
    <fields>
        <field name="HROrganization" id="HROrganization"><![CDATA[ORG#]]></field>
        <field name="Employee" id="Employee"><![CDATA[EMPLOYEE#]]></field>
        <field name="RequestedBy" id="RequestedBy"><![CDATA[REQUESTER NAME]]></field>
        <field name="RequesterEmail" id="RequesterEmail"><![CDATA[requesteremail@email.org]]></field>
        <field name="EmployeeName" id="EmployeeName"><![CDATA[EMPLOYEE NAME]]></field>
        <field name="TerminationDate" id="TerminationDate"><![CDATA[20180109]]></field>
        <field name="LastDatePaid" id="LastDatePaid"><![CDATA[20180108]]></field>
        <field name="RelationshipStatus" id="RelationshipStatus"> <![CDATA[TERMINATED]]></field>
        <field name="TerminationNoticeDate" id="TerminationNoticeDate"><![CDATA[20180109]]></field>
        <field name="ActionRequest" id="ActionRequest"><![CDATA[0160bfd1-c806-0000-003d-21e6538f85a8]]></field>
        <field name="Employee_reason_code" id="Employee_reason_code"><![CDATA[TI- POORATTEND]]></field>
        <field name="Employee_action_comment" id="Employee_action_comment"><![CDATA[]]></field>
        <field name="Employee_effective_date" id="Employee_effective_date"><![CDATA[20180109]]></field>
    </fields>
    <views></views>
</view>

Is anyone able to help me out or at least point me in the right direction?

Thanks.

Edit: I think the reason I was having issues with .node and .value is because the column is a nvarchar datatype, not XML. When I cast the column as XML it no longer has an issue with the code before I run it, but then when I do I get

Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 38, unable to switch the encoding

Code:

SELECT CAST(XMLcol AS XML).value('(view/fields/field/node())[1]', 'nvarchar(max)') AS HRORG
FROM MyTable

EDIT 2: The beginning of the XML statement was causing errors when I ran it so I did: 

SELECT 
    CAST(REPLACE(XMLCol, '<?xml version="1.0" encoding="utf-8"?>', '') AS XML).value('(view/fields/field/node())[1]', 'nvarchar(max)') AS XMLDATA
FROM 
    XMLTABLE

this returns a value of ORG#, but how do I show the other fields in the query results?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tyler
  • 1
  • 2
  • try this: https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 – jose_bacoy Mar 14 '18 at 18:34
  • First question: is it SQL Server **2008** (as mentioned in the title and the tag), or **2008 R2** (as mentioned in the question body) - those are **NOT** the same! Please make sure you're **consistent** in your versions mentioned! – marc_s Mar 14 '18 at 19:28
  • Secondly: if you're storing something that looks like XML, smells like XML, quacks like XML ....... ***WHY*** aren't you storing it in a column of datatype `XML` ?? That's the **proper** datatype and that's the datatype that **ought to be used** in this case – marc_s Mar 14 '18 at 19:28
  • Its 2008 R2, I apologize. I have no idea, our database is cloud hosted and we do not have admin privileges. Its used by an application my company purchased, I just do reporting off the data in the database. – Tyler Mar 14 '18 at 19:42

1 Answers1

0
SELECT XMLDATA.value('(view/fields/field/node())[1]', 'nvarchar(max)') AS Col1
      ,XMLDATA.value('(view/fields/field/node())[2]', 'nvarchar(max)') AS Col2
      ,XMLDATA.value('(view/fields/field/node())[3]', 'nvarchar(max)') AS Col3
      ,XMLDATA.value('(view/fields/field/node())[4]', 'nvarchar(max)') AS Col4
      ,XMLDATA.value('(view/fields/field/node())[5]', 'nvarchar(max)') AS Col5
      ,XMLDATA.value('(view/fields/field/node())[6]', 'nvarchar(max)') AS Col6

FROM (SELECT CONVERT(XML, REPLACE(XMLDATA, '<?xml version="1.0" encoding="utf-8"?>', '')) AS XMLDATA

      FROM MyTable) A
Tyler
  • 1
  • 2