xml as varchar datatype

Question

xml as varchar datatype

Tava

SSCarpal Tunnel

Points: 4114
More actions
October 23, 2012 at 11:26 pm

#265967

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tava SSCarpal Tunnel Points: 4114 More actions · Answer 1

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" >

<Template>Capital Report System</Template>

<Announcement_Title>CapitalReport</Announcement_Title>

</Headline>

<Full_News>

<Ref_No>00001</Ref_No>

<SG_ComRegNo>001</SG_ComRegNo>

<AnnouncerName>TestAnnouncement</AnnouncerName>

<Designation>TestAnnouncement</Designation>

<ContactNo>TestAnnouncement</ContactNo>

<Announcement_Details>

<ChangeInCapital>Rights</ChangeInCapital>

<Capital_AnncTitle>TestAnnouncement</Capital_AnncTitle>

<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 SSC-Forever Points: 41690 More actions · Answer 2

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
My blog - http://www.cadavre.co.uk/
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/

DanMarz Valued Member Points: 71 More actions · Answer 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 SSCarpal Tunnel Points: 4114 More actions · Answer 4

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 SSC Guru Points: 53354 More actions · Answer 5

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 SSCarpal Tunnel Points: 4114 More actions · Answer 6

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 SSCarpal Tunnel Points: 4114 More actions · Answer 7

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