InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 11:28:19 GMT20http://www.sqlservercentral.com/Forums/Topic1376325-391-1.aspxDuplicate post. Original thread can be found here:[url]http://www.sqlservercentral.com/Forums/Topic1376326-391-1.aspx[/url]Wed, 24 Oct 2012 00:29:30 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1376325-391-1.aspxHi guys,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 - ([i]Hopefully it turns out easy to read[/i])<?xml version="1.0" encoding="UTF-16"?><!DOCTYPE Announcement SYSTEM "CapReport.dtd" > <Announcement> <Template>Capital Report System</Template> <Headline> <SubmittedWrt>Test</SubmittedWrt> <Announcement_Title>CapitalReport</Announcement_Title> <BroadcastDate>24-Oct-2012</BroadcastDate> <BroadcastTime>10:02:54</BroadcastTime> </Headline> <Full_News> <Code>AAA</Code> <Ref_No>00001</Ref_No> <AnnouncedBy>ABC123</AnnouncedBy> <SG_ComRegNo>001</SG_ComRegNo> <SubmittedFor>ABC123</SubmittedFor> <SubmittedWrt>ABC123</SubmittedWrt> <AnnouncerName>TestAnnouncement</AnnouncerName> <Designation>TestAnnouncement</Designation> <BroadcastDate>24-Oct-2012</BroadcastDate> <BroadcastTime>10:02:54</BroadcastTime> <PriceSensitivity>No</PriceSensitivity> <ContactNo>TestAnnouncement</ContactNo> <SubmissionDate>24/10/2012</SubmissionDate> <SubmissionTime>10:02:29 AM</SubmissionTime> <Announcement_Details> <ChangeInCapital>Rights</ChangeInCapital> <Capital_AnncTitle>TestAnnouncement</Capital_AnncTitle> <ShareHolderApp>No</ShareHolderApp> <TextAns_Description>TestAnnouncement</TextAns_Description> <Attachment>TestAnnouncement123.pdf</Attachment> </Announcement_Details> </Full_News> </Announcement> While my staging table just has only 3 columns which are relevant to me at this point (may change in future).TableName: XMLTestwith columnsID INT -- incremented by 1 (PK)Date DATE -- BroadcastDate Time TIME -- BroadcastTimeCode 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, ThanksTavaTue, 23 Oct 2012 23:23:57 GMTTava