Post reply

Querying XML in SQL

  • March 16, 2005 at 1:03 pm

    #61538

    I've been trying to figure out how to parse account information stored in a table in XML format. I was told i shoudl use ap_xml_preparedocument and i've attempted to use it with no luck....the original table looks like this:

    id            xmlaccountinfo                                datetime

    1            <Account>    <AccountInfo ...           2005-01-18

     

    in greater detail here is a sample of the xmlaccountinfo:

    <Account>

      <AccountInfo

         DoCreditCheckOnly="1"

         DoCreateOnly="0"

         PrimaryPhoneAreaCode="123"

         PrimaryPhoneExchange="456"

         PrimaryPhoneLine="9789"

         AccountSource="ContractsDB"

         AccountStatus="Born"

         LDC="AGLC"

         StreetName="TGR"

         StreetNumber="555"

         StreetSuffix="N"

         StreetThoroughfare="ALY"

         StreetStructure="Store"

         City="AVERA"

         State="GA"

         ZipCode="55555-5555"

         FirstName="MICKEY"

         LastName="MOUSE"

         SocialSecurity="555555555"

         ContactName="MICKEY MOUSE"

         DoCreditCheck="1"

         MStreetName="TGR"

         MStreetNumber="555"

         MStreetSuffix="N"

         MStreetThoroughfare="ALY"

         MStreetStructure="Store"

         MCity="AVERA"

         MState="GA"

         MZipCode="55555-5555"

         PStreetName="TGR"

         PStreetNumber="555"

         PStreetSuffix="N"

         PStreetThoroughfare="ALY"

         PStreetStructure="Store"

         PCity="AVERA"

         PState="GA"

         PZipCode="55555-5555"

         CGS="0"

         ServiceDenied="0"

         Id="33383" />

    </Account>

     

    I'd like to query the xml and create a table with the customer info set in different columns.

     

     

     

  • March 17, 2005 at 9:34 am

    #547588

    Ramona,

    If the column in which you have the xml is not a text column you may have a little easier path, but if it is

    have a look at this (this is about the only thing you could do AFAIK)

    hth

     


    * Noel

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

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