asked on

Retrieve Data from XML and Put into Database

I am retrieving XML output as a string from a server. I am able to get this working if I remove from the string all this information:
<?xml version="1.0" encoding="UTF-8"?>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body> and if the following is removed from the QueryResponse tag: xmlns="http://blah/xml">

Instead of removing these characters via string manipulation is there an easy way to do this or am I missing something. I am using the OpenXML command.

This is a sample of the output that I receive as a string:

<?xml version="1.0" encoding="UTF-8"?>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<QueryResponse xmlns="http://blah/xml">
<TimeOfDay day="2013-07-15">
<PricingNode location="XXXX">
<PricingNodeInterval interval="2013-07-15T13:05:00.000-05:00">
<AAA>52.78</AAA>
<BBB>-1.99</BBB>
<CCC>0.24</CCC>
</PricingNodeInterval>
</PricingNode>
<PricingNode location="YYYY">
<PricingNodeInterval interval="2013-07-15T13:05:00.000-05:00">
<AAA>49.2</AAA>
<BBB>-1.99</BBB>
<CCC>-3.34</CCC>
</PricingNodeInterval>
</PricingNode>
</TimeOfDay>
</QueryResponse>
</Body>
</Envelope>

Fernando Soto

Hi mathew_s;

After removing what you need to from the XML document what should the resulting string of the data looks like?

mathew_s

ASKER

It would just be this. From this, I can use the OpenXML command. Note that I can remove with string manipulation the other elements but just wondering if there is a more elegant way:

<QueryResponse>
<TimeOfDay day="2013-07-15">
<PricingNode location="XXXX">
<PricingNodeInterval interval="2013-07-15T13:05:00.000-05:00">
<AAA>52.78</AAA>
<BBB>-1.99</BBB>
<CCC>0.24</CCC>
</PricingNodeInterval>
</PricingNode>
<PricingNode location="YYYY">
<PricingNodeInterval interval="2013-07-15T13:05:00.000-05:00">
<AAA>49.2</AAA>
<BBB>-1.99</BBB>
<CCC>-3.34</CCC>
</PricingNodeInterval>
</PricingNode>
</TimeOfDay>
</QueryResponse

Fernando Soto

Hi mathew_s;

Doing the following will give you what you need.

// The XML string in your code.
string docStr = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Envelope xmlns=\"http://schemas.xmlsoap.org/soap/envelope/\">    <Body>        <QueryResponse xmlns=\"http://blah/xml\">            <TimeOfDay day=\"2013-07-15\">                <PricingNode location=\"XXXX\">                    <PricingNodeInterval interval=\"2013-07-15T13:05:00.000-05:00\">                        <AAA>52.78</AAA>                        <BBB>-1.99</BBB>                        <CCC>0.24</CCC>                    </PricingNodeInterval>                </PricingNode>                <PricingNode location=\"YYYY\">                    <PricingNodeInterval interval=\"2013-07-15T13:05:00.000-05:00\">                        <AAA>49.2</AAA>                        <BBB>-1.99</BBB>                        <CCC>-3.34</CCC>                    </PricingNodeInterval>                </PricingNode>            </TimeOfDay>        </QueryResponse>    </Body></Envelope>";

// Convert the XML string into a XDocument, Linq to XML
XDocument doc = XDocument.Parse(docStr);
// The Namespace at the root of the document
XNamespace ns1 = "http://schemas.xmlsoap.org/soap/envelope/";
// The Namespace at the QueryResponse node
XNamespace ns2 = "http://blah/xml";
// Returns the string you are looking for.
string result = doc.Root.Element(ns1 + "Body").Element(ns2 + "QueryResponse").ToString();

Open in new window

mathew_s

ASKER

Close. Only thing I have to do is remove
xmlns="http://blah/xml" in the QueryResponse tag as this still gets returned in the result.

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer