Post reply

Extracting XML FROM a table

  • February 21, 2011 at 8:25 am

    #388906

    Here’s the situation. I have a table and one of the fields contains XML with numerous elements in it. I want to be able to separate those elements into another table giving each element (we want to analyse) its own field. I know this can be done with sting manipulation script but that seems clunky and will probably be resource intensive.

    My question is, is there a better way of doing this in SSIS? I know I could do it easily if the XML were in files and we had an XSD, but the XML I want is in a table. We should have an XSD somewhere but I haven’t looked for it yet.

    Any suggestions?

  • February 21, 2011 at 8:29 am

    #1289450

    You don't need SSIS for it.

    I'd recommend XQuery instead.

    If you could provide a sample and your expected result we could show you an example based on your specific scenario.

    Until then, all I can do is point you to a resoruce where you might find a solution that can be modified to meet your requirements: Jacob Sebastians Blog


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 21, 2011 at 8:49 am

    #1289460

    You could use SSIS, but XQuery will be more efficient.

    As well as the link Lutz gave you, MSDN and BOL have a whole section on using XML in SQL Server. Lots of very useful data in there.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 22, 2011 at 12:53 am

    #1289719

    Thanks, for the link.

    Sample Data from a field called XMLRequest in table ClientRequest -

    <apicr xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" phase="6a" entrypoint="Search06a" xmlns="urn:OurCompName.plc.uk/api5"><payload><userhostaddress xmlns="urn:OurCompName.plc.uk/private">127.0.0.1</userhostaddress></payload><thrierequest><applicant><address><abodeno/><buildingno>1</buildingno><buildingname/><street1>made up street</street1><street2>made up place</street2><sublocality/><locality/><posttown/><premiseno>1</premiseno><premisename/><postcode>zz56 4zz</postcode><duration/></address><name><title>MR</title><forename>BEN</forename><othernames/><surname>FRANKLIN</surname><suffix/></name><dob>1822-01-03</dob><tpoptout>1</tpoptout></applicant><score>1</score><purpose>AA</purpose><transient>0</transient></theirrequest></apicr>

    Basic query that might be run at the end -

    select Phase, EntryPoint, PostCode, Title, Forename, Surname, Score, Purpose

    from ResultsTable

  • February 22, 2011 at 6:49 am

    #1289832

    Try this example:

    http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/69b87bea-a943-4e53-8bfc-6a5e9213c2f4

    Please mark as answer if you find it helpful.

  • February 22, 2011 at 10:26 am

    #1289953

    oconnor_dp (2/22/2011)

    Try this example:

    http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/69b87bea-a943-4e53-8bfc-6a5e9213c2f4

    Please mark as answer if you find it helpful.

    Is there any specific reason for posting a solution on another website for a question asked here??


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 23, 2011 at 1:12 am

    #1290254

    I take LutzM's point, but you've helped me tremendously so thanks. Never having to touch XML previously this would have taken an age to figure it out myself.

  • February 24, 2011 at 9:13 am

    #1291058

    wildh (2/21/2011)

    Here’s the situation. I have a table and one of the fields contains XML with numerous elements in it. I want to be able to separate those elements into another table giving each element (we want to analyse) its own field. I know this can be done with sting manipulation script but that seems clunky and will probably be resource intensive.

    My question is, is there a better way of doing this in SSIS? I know I could do it easily if the XML were in files and we had an XSD, but the XML I want is in a table. We should have an XSD somewhere but I haven’t looked for it yet.

    Any suggestions?

    You can use the standard Export Column transformation, to extract the XML in separate file. Then use the standard XML Source component to read it and process it.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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