select nodes from a line of xml code with sql
- by wondergoat77
I have a table that stores a huge line/entire document of xml like this:
<?xml version="1.0" encoding="utf-16"?> <RealQuestResponse xmlns:xsi="http://www.w3.org /2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Success>true</Success> <Subject> <AmbiguousMatches /> <Assessment> <LandValue>0</LandValue> <ImprovementsValue>0</ImprovementsValue> <TotalValue>0</TotalValue> </Assessment> <RecentSales /> <Warnings> <Score>0</Score> <TrusteesDeedRatio>0</Tr........etc
Is there a way to pull any of these fields out of the xml? it is stored in a column in a table called AutomatedRequests
That table looks like this:
requestid Provider Date Success Response
1 test 1/2/2012 Y <?xml version..... <---this is the xml code stored>
Ive seen a couple ways but nothing like this
Id basically like something like
select xmlnode1, xmlnode2, xmlnode3 from automatedrequests
have tried this but not working:
select xml.query('RealQuestResponse/Bedrooms/*')
from automatedRequests where orderid = 1266162
