xml as varchar datatype

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » xml as varchar datatype

xml as varchar datatype

Rate Topic

Display Mode

Topic Options

Author

Message

Tava

Posted Tuesday, October 23, 2012 11:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:37 PM
Points: 76, Visits: 294

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

Tava

Posted Wednesday, October 24, 2012 12:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:37 PM
Points: 76, Visits: 294

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

Cadavre

Posted Wednesday, October 24, 2012 3:16 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

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

Post #1376374

DanMarz

Posted Wednesday, October 24, 2012 3:24 AM

Forum 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

Tava

Posted Wednesday, October 24, 2012 7:37 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:37 PM
Points: 76, Visits: 294

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

The Dixie Flatline

Posted Wednesday, October 24, 2012 7:54 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:41 AM
Points: 3,788, Visits: 5,538

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

Tava

Posted Wednesday, October 24, 2012 8:23 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:37 PM
Points: 76, Visits: 294

The Dixie Flatline (10/24/2012)

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

Tava

Posted Thursday, October 25, 2012 12:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:37 PM
Points: 76, Visits: 294

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 »

Permissions