Post reply

Parse XML data type to individual columns

  • November 7, 2013 at 4:33 pm

    #285839

    I have the following XML stored in one of my table columns. I need to parse it and get the address into 6 separate columns

    StreetAddress, City, State, Zip, County, AdditionalPostalCode

    Here is the sample content of the XML column

    <Customer>

    <CUSTINFO _StreetAddress="101 MAIN STREET" _City="LOS ANGELES" _State="CA" _PostalCode="90001" _County="LOS ANGELES" _PlusFourPostalCode="1001">

    <_CUSTDESCRIPTION _SSN="123-45-6789">

    <_CUSTNAME _FirstName="John" _MiddleInitial="X" _LastName="Smith" />

    </_CUSTDESCRIPTION>

    <_PERSONALINFO _MaritalStatus="M" _Sex="M" _NumberOfKids="2" _AnnualIncome="156733.82" />

    </CUSTINFO>

    </Customer>

    Thanks in advance.

  • November 7, 2013 at 5:21 pm

    #1664968

    The code below works (substitute table column name for @x in the code below), but only if each element contains one or less of each child element. As in, there is only one <CUSTINFO> element as a child of the only <Customer> element in the XML document or fragment. If there are multiple <CUSTINFO> elements under <Customer>, then this code will only retrieve the data from the first one.

    DECLARE @x xml = '<Customer>

    <CUSTINFO _StreetAddress="101 MAIN STREET" _City="LOS ANGELES" _State="CA" _PostalCode="90001" _County="LOS ANGELES" _PlusFourPostalCode="1001">

    <_CUSTDESCRIPTION _SSN="123-45-6789">

    <_CUSTNAME _FirstName="John" _MiddleInitial="X" _LastName="Smith" />

    </_CUSTDESCRIPTION>

    <_PERSONALINFO _MaritalStatus="M" _Sex="M" _NumberOfKids="2" _AnnualIncome="156733.82" />

    </CUSTINFO>

    </Customer>';

    SELECT @x.value('(Customer/CUSTINFO/@_StreetAddress)[1]', 'varchar(128)') AS [StreetAddress],

    @x.value('(Customer/CUSTINFO/@_City)[1]', 'varchar(128)') AS [City],

    @x.value('(Customer/CUSTINFO/@_State)[1]', 'char(2)') AS [State],

    @x.value('(Customer/CUSTINFO/@_PostalCode)[1]', 'varchar(6)') AS [ZIP],

    @x.value('(Customer/CUSTINFO/@_County)[1]', 'varchar(128)') AS [County],

    @x.value('(Customer/CUSTINFO/@_PlusFourPostalCode)[1]', 'varchar(4)') AS [AdditionalPostalCode];

    Eddie Wuerch
    MCM: SQL

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

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