CODEWITHSUNDEEP
sqlxmlstored-procedures
Paul Coan
Paul Coan

Reputation: 302

Extracting XML Data with SQL

I have the following XML data stored in a SQL table

<CustomFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns="http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd">
<Field fieldName="ChangeRequest">No</Field>
<Field fieldName="ProblemRecord">No</Field>
<Field fieldName="Source">Email</Field>
<Field fieldName="KB_Article">No</Field>
<Field fieldName="OptimusRef">264692</Field>
<Field fieldName="TimeSpentOnTicket">0.25</Field>
<Field fieldName="PONumber" />
<Field fieldName="ResourceAssignedEngineer" />

What I would like to do is select the TimeSpentOnTicket Value form a stored procedure.

Any ideas how I can do this?

Upvotes: 2

Views: 65

Answers (1)

Justin Grant
Justin Grant

Reputation: 123

The problem here is your XML. It's invalid, so there's not really a way do search it until you fix it. A simple way to check this is using an online tool like the one at W3Schools. Another issue that I see is that the namespace (xmlns) that you reference no longer exists. I think this will mess up Postgres as well, but I'm not 100% on that. You might to have to filter that out when ingesting. However, after fixing the XML, it's pretty easy to get things out using XPath within the appropriate XML function.

For example, using the following table:

CREATE TABLE BLA.TEMPTABLE (ID INT, MYXML XML)

Then, insert a valid version of your XML:

INSERT INTO BLA.TEMPTABLE ( ID, MYXML )
SELECT 1 as ID, 
    '<?xml version="1.0" encoding="UTF-8"?>
    <CustomFields>
        <Field fieldName="ChangeRequest">No</Field>
        <Field fieldName="ProblemRecord">No</Field>
        <Field fieldName="Source">Email</Field>
        <Field fieldName="KB_Article">No</Field>
        <Field fieldName="OptimusRef">264692</Field>
        <Field fieldName="TimeSpentOnTicket">0.25</Field>
        <Field fieldName="PONumber" />
        <Field fieldName="ResourceAssignedEngineer" />
    </CustomFields>' as MYXML

Then, to query it back out, you can do something like the following (you can test your XPath with a tool like this, if you need to):

SELECT
    tt.ID, 
    tt.MYXML,
    XPATH('/CustomFields//Field[@fieldName=''TimeSpentOnTicket'']/text()', tt.MYXML)
FROM
    BLA.TEMPTABLE tt

Upvotes: 1

Related Questions