Post reply

Load xml file to database and query against the contents

  • March 22, 2007 at 11:01 am

    #68475

    Hi folks, I am at a complete loss. I have three xml files from an external system that I need to tie together ( much like you would three database tables ). I have been able to load the xml files into a table so that the entire document in stored in one filed of type "xml" (see below for definition). My problem is that I have not been able to get any results back out of the table using XQuery.

    Any suggestions (examples) would be greatly appreciated. I have read everything I can find on the web but still can't determine what it that I need to do in order to be able to extract a value based on tag it is in and its reference. Below is the statement I used to create the table and an excerpt of the xml data.

    Thanks in advance for your feedback.

    CREATE TABLE source (map_name varchar(50), XmlCol xml)

    GO

    INSERT INTO source(map_name,XmlCol)

    SELECT 'RPD_850_003030_V7',* FROM OPENROWSET(

    BULK 'C:\junk\RPD_850_003030_V7\bdd\X12-850-003030_V1.xml',

    SINGLE_BLOB) AS x

    -- my apologies if the xml data does show up, I am having trouble getting it to display.

    -->

  • March 22, 2007 at 4:05 pm

    #696501

    since you dumped the entire document in a single column, you can study XQuery:

    http://msdn2.microsoft.com/en-us/library/ms189075.aspx

    alternatively, you could use xml bulk load to import the xml into relational tables.  then you could query it in the normal way you would any other table:

    http://msdn2.microsoft.com/en-us/library/ms171993.aspx

    ---------------------------------------
    elsasoft.org

  • March 23, 2007 at 7:37 am

    #696602

    Thanks, I have tried XQuery (alot) but for some reason I can't get it to work against my data.

    Here is a protion of the data.......any suggestions ether with XQuery or how to properly get it loaded with xml bulk load?

    (Ignore the "@=" at the beginning and end, they are just there because I was having troubles getting the data to display here.)

    @=<?xml version="1.0" encoding="UTF-8"?>

    <schema>

    <element  name="X12-850-003030_V1.xml!76a746e6d439342a4213" DecChar="." DecCharList="." DefaultAdapter="N" Description="PURCHASE ORDER" DocType="0" EDIVersion="003030" Formatter="com.harbinger.mek.mk.adapter.x12.compiler.HLMCEDIX12Formatter" MVersion="3-00" NegSignPre="-" NumPadding="3" Parser="com.harbinger.mek.mk.adapter.x12.compiler.HLMCEDIX12Parser" RefName="X12-850-003030_V1" UID="76a746e6d439342a4213" Validator="com.harbinger.mek.mk.adapter.x12.validate.HLEDIX12Validation" dataStandard="X12" dataType1="EDI" extractRunID="76a746e6e5acb09b8000!Mon Jun 04 18:02:57 CDT 2001" extractStdkey="161!850" jarFileName="x12.jar" type="X12-850-003030_V1.xml!76a746e6d439342a4213"/>

      <type name="X12-850-003030_V1.xml!76a746e6d439342a4213">

         <element name="ISA!76a746e6d439342a4212" Description="Interchange Control Header" EnvRecType="1" ExpandNode="N" RefName="ISA" UID="76a746e6d439342a4212" maxOccurs="1" minOccurs="1" type="ISA!76a746e6d439342a4212"/>

         <element name="GS!76a746e6d439342a4201" Description="Functional Group Header" EnvRecType="2" ExpandNode="N" RefName="GS" UID="76a746e6d439342a4201" maxOccurs="1" minOccurs="0" type="GS!76a746e6d439342a4201"/>

         <element name="ST!76a746e6d439342a41f2" Description="TRANSACTION SET HEADER" EnvRecType="3" RefName="ST" UID="76a746e6d439342a41f2" maxOccurs="1" minOccurs="1" type="ST!76a746e6d439342a41f2"/>

         <element name="SE!76a746e6d439342a3dbf" Description="TRANSACTION SET TRAILER" EnvRecType="5" ExpandNode="N" RefName="SE" UID="76a746e6d439342a3dbf" maxOccurs="1" minOccurs="1" type="SE!76a746e6d439342a3dbf"/>

         <element name="GE!76a746e6d439342a41f8" Description="Functional Group Trailer" EnvRecType="6" ExpandNode="N" RefName="GE" UID="76a746e6d439342a41f8" maxOccurs="1" minOccurs="0" type="GE!76a746e6d439342a41f8"/>

         <element name="IEA!76a746e6d439342a41f5" Description="Interchange Control Trailer" EnvRecType="7" ExpandNode="N" RefName="IEA" UID="76a746e6d439342a41f5" maxOccurs="1" minOccurs="0" type="IEA!76a746e6d439342a41f5"/>

      </type>

      <type name="ISA!76a746e6d439342a4212">

         <element name="I01!76a746e6d439342a4211" Description="Authorization Information Qualifier" EnvName="ISA01" MaxLen="2" MinLen="2" RefName="ISA01" StringType="3" UID="76a746e6d439342a4211" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I02!76a746e6d439342a4210" Description="Authorization Information" EnvName="ISA02" MaxLen="10" MinLen="10" RefName="ISA02" StringType="3" UID="76a746e6d439342a4210" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I03!76a746e6d439342a420f" Description="Security Information Qualifier" EnvName="ISA03" MaxLen="2" MinLen="2" RefName="ISA03" StringType="3" UID="76a746e6d439342a420f" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I04!76a746e6d439342a420e" Description="Security Information" EnvName="ISA04" MaxLen="10" MinLen="10" RefName="ISA04" StringType="3" UID="76a746e6d439342a420e" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I05!76a746e6d439342a420d" Description="Interchange ID Qualifier" EnvName="ISA05" MaxLen="2" MinLen="2" RefName="ISA05" StringType="3" UID="76a746e6d439342a420d" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I06!76a746e6d439342a420c" Description="Interchange Sender ID" EnvName="ISA06" MaxLen="15" MinLen="15" RefName="ISA06" StringType="3" UID="76a746e6d439342a420c" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I05!76a746e6d439342a420b" Description="Interchange ID Qualifier" EnvName="ISA07" MaxLen="2" MinLen="2" RefName="ISA07" StringType="3" UID="76a746e6d439342a420b" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I07!76a746e6d439342a420a" Description="Interchange Receiver ID" EnvName="ISA08" MaxLen="15" MinLen="15" RefName="ISA08" StringType="3" UID="76a746e6d439342a420a" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I08!76a746e6d439342a4209" Description="Interchange Date" DtFmt="yyMMdd" EnvName="ISA09" MaxLen="6" MinLen="6" RefName="ISA09" UID="76a746e6d439342a4209" controlType="8" maxOccurs="1" minOccurs="1" type="date"/>

         <element name="I09!76a746e6d439342a4208" Description="Interchange Time" EnvName="ISA10" MaxLen="4" MinLen="4" RefName="ISA10" TmFmt="HHmm" UID="76a746e6d439342a4208" controlType="9" maxOccurs="1" minOccurs="1" type="time"/>

         <element name="I10!76a746e6d439342a4207" Description="Interchange Control Standards Identifier" EnvName="ISA11" MaxLen="1" MinLen="1" RefName="ISA11" StringType="3" UID="76a746e6d439342a4207" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I11!76a746e6d439342a4206" Description="Interchange Control Version Number" EnvName="ISA12" MaxLen="5" MinLen="5" RefName="ISA12" StringType="3" UID="76a746e6d439342a4206" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I12!76a746e6d439342a4205" Description="Interchange Control Number" EnvName="ISA13" MaxLen="9" MinLen="9" RefName="ISA13" StringType="3" UID="76a746e6d439342a4205" controlType="5" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I13!76a746e6d439342a4204" Description="Acknowledgment Requested" EnvName="ISA14" MaxLen="1" MinLen="1" RefName="ISA14" StringType="3" UID="76a746e6d439342a4204" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I14!76a746e6d439342a4203" Description="Test Indicator" EnvName="ISA15" MaxLen="1" MinLen="1" RefName="ISA15" StringType="3" UID="76a746e6d439342a4203" maxOccurs="1" minOccurs="1" type="hlfs"/>

         <element name="I15!76a746e6d439342a4202" Description="Component Element Separator" EnvName="ISA16" MaxLen="1" MinLen="1" RefName="ISA16" StringType="3" UID="76a746e6d439342a4202" maxOccurs="1" minOccurs="1" type="hlfs"/>

      </type>

      <type name="GS!76a746e6d439342a4201">

         <element name="0479!76a746e6d439342a4200" Description="Functional Identifier Code" EnvName="GS01" MaxLen="2" MinLen="2" RefName="GS01" StringType="3" UID="76a746e6d439342a4200" maxOccurs="1" minOccurs="1" type="string"/>

         <element name="0142!76a746e6d439342a41ff" Description="Application Sender's Code" EnvName="GS02" MaxLen="15" MinLen="2" RefName="GS02" StringType="3" UID="76a746e6d439342a41ff" maxOccurs="1" minOccurs="1" type="string"/>

         <element name="012=@

  • March 23, 2007 at 9:22 am

    #696643

    actually you didn't post any of your xml.

    all you've got above is part of an xml schema.  the actual data would come after that...

    ---------------------------------------
    elsasoft.org

  • March 23, 2007 at 10:07 am

    #696655

    My apologies, here you go.

    <?xml version="1.0" encoding="UTF-8"?>

      <HLMetaDataBDT MVersion="3-00" RefName="RPD_850_003030_V7" Source="X12-850-003030_V1.xml" Target="ZORDERS_BS_3030v1.xml" UID="2d189ce7e5abbb7d71d1" maxOccurs="1" name="RPD_850_003030_V7.xml">

         <RecLink SUID="76a746e6d439342a4212" TUID="2d189ce7e5abbb7d73de"/>

         <MapLink SUID="76a746e6d439342a41f0" TUID="2d189ce7e5abbb7d73db"/>

         <Formula TUID="2d189ce7e5abbb7d73d5" Value=""/>

         <MapLink SUID="76a746e6d439342a41e5" TUID="2d189ce7e5abbb7d73d0"/>

         <MapLink SUID="76a746e6d439342a41e4" TUID="2d189ce7e5abbb7d73d0"/>

         <Formula TUID="2d189ce7e5abbb7d73d0" Value="find NTE01A where (NTE01A=&quot;ORI&quot eval (if (left(NTE02A, 4)=&quot;CPAL&quot;, &quot;CP&quot) || find NTE01A where (NTE01A=&quot;ORI&quot eval (if (left(NTE02A, 3)=&quot;CDP&quot;, &quot;CP&quot) "/>

         <Formula TUID="2d189ce7e5abbb7d73cf" Value="&quot;X&quot;"/>

         <MapLink SUID="76a746e6d439342a41f9" TUID="2d189ce7e5abbb7d73ce"/>

         <Formula TUID="2d189ce7e5abbb7d73ce" Value=""/>

     

  • March 23, 2007 at 10:23 am

    #696660

    Now you need to tell us what value you are trying to extract.  what XQuery have you tried so far?

    ---------------------------------------
    elsasoft.org

  • March 23, 2007 at 10:41 am

    #696664

    right now I am trying to get the basics working such as the following query

    select xmlcol.query('data(/HLMetaDataBDT/MapLink)') from map

    At the end of the day, I have the schemas for input and ouput layouts, both stored in an xml type column, use the xml data to tie the two together and show me value of Refname from each. 

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

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