Pulling data from a query as xml

Situation:

I have a map that does a query on a database. The value returned from the  database query is actually xml. I need to get the value from one of the  nodes in the xml document. Since the query returns its value as a string, how can I parse this as xml using an xpath to get the specific value I need?

My query (Select Message from tblHDSLog where Id = ‘139’) returns the following value, which is stored in the database in column “Message”:

< Order
xmlns:xsd = " http://www.w3.org/2001/XMLSchema "
xmlns:xsi = " http://www.w3.org/2001/XMLSchema-instance " >
< MessageHeader >
< FromPartnerID > Partner </ FromPartnerID >
< ToPartnerID > Adeptia </ ToPartnerID >
< MessageDateTime > 2012-08-23T15:34:42.7207522-04:00 </ MessageDateTime >
</ MessageHeader >
< OrderHeader >
< OrderHeaderId > 1-AAAATG </ OrderHeaderId >
< ServiceRequestNum > AA01063283 </ ServiceRequestNum >
< OrderNumber > 1-777707684 </ OrderNumber >
< EmployeeId > CPDSS </ EmployeeId >
< EngineerFirstName > John </ EngineerFirstName >
< EngineerLastName > Doe </ EngineerLastName >
< Currency > USD </ Currency >
< OrderStatus > In
Progress </ OrderStatus >
< ScheduledDeliveryDate > 2012-08-23T15:34:42.7207522-04:00 </ ScheduledDeliveryDate >
< Notes > Test
Notes </ Notes >
</ OrderHeader >
< LineItems >
< LineItem >
< OrderLineItemId > 1-DAAAA </ OrderLineItemId >
< Quantity > 1 </ Quantity >
< PartName > 001-001455 </ PartName >
< OrigProdId > 1-9YX2P6 </ OrigProdId >
</ LineItem >
< LineItem >
< OrderLineItemId > 09812 </ OrderLineItemId >
< Quantity > 1 </ Quantity >
< PartName > Part
2 </ PartName >
< OrigProdId > 3213213 </ OrigProdId >
</ LineItem >
</ LineItems >
< OrderTrailer >
< TotalQuantity > 1 </ TotalQuantity >
</ OrderTrailer >
</ Order >

I then need to get the value for /Order/LineItems/LineItem[OrderLineItemId=’09812’]/OrigProdId.

 

Also, I need the opposite function also….to turn a node list to a string. ( I need to map the entire xml document to a sqlserver table, storing
the xml document in a column called “Message”.)

 

Solution:

String to XML

Use the attached custom class. This has a method getDataFromXMLString(String xmlStr) in it which will accept one input as below:
1st Parameter : XML in string format

The function will return a valid XML document. So that it can be easy to iterate the XPath on it as per requirement.

XML to String

We have added a function getStringfromXML(Object doc). it will accept one input as below:
Input Parameter : XML Document

The function will return a string for the respective XML document.

Please find the attached XMLAsString.class file.

Place this class file in the /ServerKernel/customClasses/ folder and restart the server.

Have more questions? Submit a request

0 Comments

Article is closed for comments.