SQL Server - How to parse a node in xml that present in text (datatype) in sql server

Asked By balaji mogadali on 18-May-17 10:24 AM
Hi frds,

I had a sql table (ProviderRequest) which store Authorize.Net Response to this table, it store in column having text as datatype. I want to parse one of the node , pls help. I provide the entire xml data . I need AuthCode and CustomProperty Value.

<ResponseObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Type>CreditCard</Type>
  <Status>Success</Status>
  <StatusDetails>This transaction has been approved.</StatusDetails>
  <Provider>Authorize.Net</Provider>
  <AuthCode>W4RIZG</AuthCode>
  <Amount>22.00</Amount>
  <InvoiceNumber>CIRE001023_514214</InvoiceNumber>
  <MD5Hash>043E6FAE5B5213A2B7D08C0665D920</MD5Hash>
  <TransId>60021797792</TransId>
  <RespCode>Approved</RespCode>
  <RespReasonCode>1</RespReasonCode>
  <CustomProperties>
    <CustomProperty>
      <Key>finys_polNum</Key>
      <Value>CIRE001023</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_guid</Key>
      <Value>7e2c479e-4a9a-4cf9-9f79-0a79b8fc3787</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_prrId</Key>
      <Value>514214</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_caller</Key>
      <Value>manualPayments</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_toEmails</Key>
      <Value>a@a.com</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_postingDate</Key>
      <Value>4/11/2017</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_paymentDate</Key>
      <Value>4/11/2017</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_dueDate</Key>
      <Value />
    </CustomProperty>
    <CustomProperty>
      <Key>finys_payTo</Key>
      <Value>Conifer Insurance</Value>
    </CustomProperty>
    <CustomProperty>
      <Key>finys_amount</Key>
      <Value>22</Value>
    </CustomProperty>
  </CustomProperties>
  <Confirmation>W4RIZG</Confirmation>
  <Method>CREDITCARD</Method>
  <PolicyNumber>CIRE001023</PolicyNumber>
  <ToEmails>a@a.com</ToEmails>
</ResponseObject>



Robbe Morris replied to balaji mogadali on 21-May-17 05:47 PM
Is there any particular reason you don't parse their response into column values prior to inserting the whole response into a table column?

That would enable you to work with this much, much more efficiently.   That said, you could just use charindex looking for the start of the node start tag and also find the node end tag.  Then replace the node start and end names leaving you with the value.

This all assumes you need to do it in sql server versus parsing it with an XML parser in your application.
balaji mogadali replied to Robbe Morris on 21-May-17 11:22 PM
Hi morris, I need to create a report based on successful transaction using authorize.net. So I need to parse XML in sql query itself. Can you help me in that? Thanks
Robbe Morris replied to balaji mogadali on 22-May-17 07:27 AM
Your report is going to be very, very slow.  But here is an example for parsing xml in sql server and converting it to a table in memory.  Really you should do this up front at the time of the transaction.  Then, write a small script/app to go through all your past records and parse the xml into normal column values in a fixed table.

http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server