Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

xml as varchar datatype Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 11:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:18 PM
Points: 90, Visits: 418
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
Post #1376325
Posted Wednesday, October 24, 2012 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
Duplicate post. Original thread can be found here:

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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1376334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse