xml as varchar datatype

• Tava

  SSCarpal Tunnel

  Points: 4118

  More actions

  October 23, 2012 at 11:23 pm

  #263775

  Hi 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 - (Hopefully it turns out easy to read)

  <?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: XMLTest

  with columns

  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,

  Thanks

  Tava

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  October 24, 2012 at 12:29 am

  #1552143

  Duplicate post. Original thread can be found here:

  http://www.sqlservercentral.com/Forums/Topic1376326-391-1.aspx

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP