Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xml as varchar datatype


xml as varchar datatype

Author
Message
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 777
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
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 777
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
Cadavre
Cadavre
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2910 Visits: 8448
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);




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
DanMarz
DanMarz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 777
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....
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3664 Visits: 6899
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? Everybody look what's going down. -- Stephen Stills
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 777
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.
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 777
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search