I need some help (well a lot) with XML. I've never used it before so am basically green in this area and learning what I read.
I have an audit table which stores XML data but datatype is varchar(max) and it can't be changed as it's controlled by a 3rd party but i need to capture key informaiton from this as its the only place its stored.
Im trying to capture the information and put it into a staging table i've made. The XML is mocked up like this - (Hopefully it turns out easy to read)
<?xml version="1.0" encoding="UTF-16"?>
<!DOCTYPE Announcement SYSTEM "CapReport.dtd" >
<Template>Capital Report System</Template>
While my staging table just has only 3 columns which are relevant to me at this point (may change in future).
ID INT -- incremented by 1 (PK)
Date DATE -- BroadcastDate
Time TIME -- BroadcastTime
Code VARCHAR(8) -- Code (AAA)
So i need to insert the relevant XML information into this XMLTest table so i can then do what im actually trying to do.
Any help be appreciated,