Post reply

How to parse a simple xml string

  • February 19, 2010 at 7:20 am

    #215868

    I am trying to decode XML from an audit table into its separate columns.

    An example of this xml is :

    <inserted u_user_id="{2f0a2500-1c1d-456d-83f2-ca48dbed642d}" u_user_type="1" u_first_name="f" u_last_name="f" u_email_address="franco.aresu@snowvalley.com" u_addresses="1;{12a66609-78cf-4089-a9b9-c40ddaf20aea}" u_tel_number="654" u_user_security_password="3A5A78D01E297B799998D33894C30F4D108BE7C16DE851B60C1130247267F92A70BDD3E3" u_account_status="1" dt_date_last_changed="2010-02-19T13:05:40" dt_date_created="2010-02-19T13:05:27" u_password_question="NoPasswordQuestion" u_password_answer="57F12FC1EDC1056789C6D352D893993E9209D0117036B8E37259B5B8E9E26DB88340E70B" b_change_password="0" dt_date_last_password_changed="2010-02-19T13:05:27" dt_last_logon="2010-02-19T13:05:27" dt_last_lockedout_date="1754-01-01T00:00:00" dt_last_activity_date="2010-02-19T13:05:27" b_direct_mail_opt_out="0" u_preferred_billing_address="{12a66609-78cf-4089-a9b9-c40ddaf20aea}" u_preferred_shipping_address="{12a66609-78cf-4089-a9b9-c40ddaf20aea}" u_user_number="4092" dt_date_of_birth="1968-11-05T00:00:00" b_third_party_contact_opt_out="1" u_title="Mrs" u_rawextension="<?xml version="1.0"?><VanillaUser xmlns="http://snowvalley.com/VanillaUser.xsd&quot; />" u_mobile_tel_number="654" u_gender="Male" i_order_count="0" i_total_visit_count="0">

    <u_x005F_xmlextension>

    <VanillaUser xmlns="http://snowvalley.com/VanillaUser.xsd" />

    </u_x005F_xmlextension>

    </inserted>

    How would I parse this XML into a results table to query from ?

    Thanks,

    Kwasi

  • February 19, 2010 at 10:40 am

    #1122002

    Here's an example with a subset of your data:

    DECLARE @xml XML

    SET @xml='

    <inserted

    u_user_id="{2f0a2500-1c1d-456d-83f2-ca48dbed642d}"

    u_user_type="1">

    <u_x005F_xmlextension>

    <VanillaUser xmlns="http://snowvalley.com/VanillaUser.xsd" />

    </u_x005F_xmlextension>

    </inserted>'

    SELECT

    c.value('@u_user_id[1]', 'varchar(50)') AS u_user_id,

    c.value('@u_user_type[1]', 'varchar(50)') AS u_user_type,

    c.query('u_x005F_xmlextension') AS u_x005F_xmlextension

    FROM @xml.nodes('inserted') T(c)

    For details please search this site for "XML Workshop Jacob Sebastian". I'm sure you'll find an interesting list of articles covering SQL Server related xml subjects.


    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]

Viewing 2 posts - 1 through 1 (of 1 total)

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