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:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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 #1376326
Posted Wednesday, October 24, 2012 12:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
I've tried the following from what i read and turned it into an XML, I believe anyway but could be totally wrong - still though even though i've turned it into an XML still no closer to inserting it into a table.



DECLARE @t TABLE
(
XMLData VARCHAR(8000)
)

INSERT INTO @t(XMLData)
VALUES
(
'<?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>'
)

DECLARE @xVar XML
SET @xVar = (SELECT * FROM @t FOR XML AUTO,TYPE)

SELECT @xVar
Post #1376330
Posted Wednesday, October 24, 2012 3:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 2,443, Visits: 7,553
Something like this: -

SELECT  
realXML.value('(/Announcement/Headline/BroadcastDate)[1]', 'DATE') AS [Date],
realXML.value('(/Announcement/Headline/BroadcastTime)[1]', 'TIME') AS [Time],
realXML.value('(/Announcement/Full_News/Code)[1]', 'VARCHAR(8)') AS [Code]
FROM (SELECT CONVERT(XML,CAST(XMLData AS NVARCHAR(MAX)), 2)
FROM @t)a(realXML);




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376374
Posted Wednesday, October 24, 2012 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 3:35 AM
Points: 1, Visits: 3
For broadcastdate in header, you could simply query the source, which has the value stored as an nvarchar(max) like this to return the value.

select CONVERT(XML, XmlDump, 2).value('(/Announcement/Headline/BroadcastDate)[1]','DATE')
from XmlTestSource
Post #1376378
Posted Wednesday, October 24, 2012 7:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
Thanks for the help both of you - i got it working but i have 1 issue if there is multiple attachments

<Attachment>TestAnnouncement123.pdf</Attachment>
<Attachment>TestAnnouncement999.pdf</Attachment>
<Attachment>TestAnn234.pdf</Attachment>

Im inserting into Audit table

INSERT INTO [dbo].[Audit]
(Description,attachment)

SELECT

realXML.value('(/Announcement/Template)[1]', 'VARCHAR(50)') AS [Description],
realXML.value('(/Announcement/Full_News/Announcement_Details/Attachment)[1]', 'VARCHAR(100)')AS [Attachment],

FROM
(
SELECT
CONVERT(XML,CAST(AnnouncementXML AS NVARCHAR(MAX)), 2)
FROM
@t
)a
(realXML);


It will only pick up the first attachment, i know i need to do Attachment[1] , Attachment[2] up to [4] etc.. but i dont know how to incorporate it in the code....
Post #1376742
Posted Wednesday, October 24, 2012 7:54 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
If you may have a variable number of attachments, you can use CROSS APPLY with the .nodes method to break out multiple nodes into multiple rows. The nodes will be XML as well, so use the .value method to get the final values. You can find examples here:

http://msdn.microsoft.com/en-us/library/ms188282.aspx


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1376744
Posted Wednesday, October 24, 2012 8:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
The Dixie Flatline (10/24/2012)
If you may have a variable number of attachments, you can use CROSS APPLY with the .nodes method to break out multiple nodes into multiple rows. The nodes will be XML as well, so use the .value method to get the final values. You can find examples here:

http://msdn.microsoft.com/en-us/library/ms188282.aspx




Thank you, I did see this while searching but didn't know if it was right or not... Ill give this a go but sounds like what I need... Little confused so Thanks for the site.
Post #1376748
Posted Thursday, October 25, 2012 12:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
All done... thanks everyone for your help... it might be the worst piece of code written but it works & i go no idea what i need to do.

Let me know know if you would like me to post it - if yes ill put it up but just need to change the names to something else.

Thanks again
Tava
Post #1376801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse