importing xml

[Darth Vodka]

hi

i have an xml from the powers that be in IT: can post it here if anyone wants, but it's quite big

tried to import it into Access 2003, no joy

than i realised that it it's almost certainly attribute-centric

http://support.microsoft.com/kb/285329

and so i need to create a template to make it look normal. although they give an example on the microsoft page, it's bespoke to the xml they create from Northwind and i'm struggling to write a template from scratch



the xml appears to have tables embedded and all sorts, it's not just a flat file structure....

can that be reversed out with a template?

 

[John.Woody]

Have a look at this post
http://www.access-programmers.co.uk/forums/showthread.php?t=116258
The last post gives code which will work its way through an xml doc and read the attributes. You can capture the data you need and drop it in to the correct fields in a form or open a recordset and drop it in to tables.

HTH
John

 

[Darth Vodka]

Have a look at this post
http://www.access-programmers.co.uk/forums/showthread.php?t=116258
The last post gives code which will work its way through an xml doc and read the attributes. You can capture the data you need and drop it in to the correct fields in a form or open a recordset and drop it in to tables.

HTH
John

oh thanks, it's giving something...table and the number one a lot..

i think maybe my xml is different? here's the opening part

- <xml>
- <Table name="TradeList">
- <Table name="Trade">
- <Table name="Transactional">
  <Row VerifyUser="summitftsuper" InputUser="summitftsuper" DoneUser="summitftsuper" CancelUser="" AmendUser="summitftsuper" VerifyDateTime="09/01/08 15:08" InputDateTime="08/01/08 15:08" DoneDateTime="09/01/08 15:08" CancelDateTime="" AmendDateTime="23/01/08 11:48" AddedValue="0" Broker="" IsInternalDeal="N" IsLeadTrade="N" ExternalTradeID="2241228LT" LinkedTradeID="" DealID="" IsCompounding="N" IsCptyConfReceived="N" IsBkrConfReceived="" dmOwnerTable="MUST_TR" Company="LTSBLN" IsPayer="" CurrencyPair="USDJPY" TASS="" TermAssignStatus="" GDIProduct="06100_CALLABLE.PRDC" Currency="" Notional="" Start_Date="06/09/07" Maturity="06/09/27" Accrual_Basis="" Holiday_Calendar="" Option_Type="Bermudan" /> 
  </Table>
- <Table name="Non_Economic_Data">
  <Row Product_Type="PRDC" Variant="" BuySell="Buy" Counterparty="ZONLADS" Rating="" Agency="" Trader="" Sales_Person="" Book="HFUSJPE" Trade_Date="26/09/07" Sales_Method="" Sales_Location="" System="" Trade_Id="2241668LT" Structure_Id="" Version="3" Amended="23/01/08" Amender="summitftsuper" Memo="" State="VER" Break_Type="MUTUAL;MUTUAL" Break_Date="02/12/16;03/12/26" Desk="GHFX" Folder="" GDIProduct="06100_CALLABLE.PRDC" /> 
  </Table>
- <Table name="Trade_Economics">
- <Table name="Leg_1">
- <Table name="Leg_1_Params">
  <Row Currency="JPY" Margin="" Frequency="S" Accrual_Basis="A365F" Is_Adjusted="Y" Notional="10000000000" Start_Date="06/09/07" Maturity="06/09/27" Roll_Convention="MF" Holiday_Calendar="NY+TOK" Stub_Type="" First_Full_Roll_Date="" Index_Gearing="" Index="USDJPYSPOT" Observation="FX" Spot_Days="-14D" Observation_Offset="-14D" Cap="" Floor="0" Spread="" /> 
  </Table>
- <Table name="Structured_Leg">
  <Row Accrual_Start="06/09/07" Accrual_End="06/03/08" Accrual_Basis="A365F" Payment_Date="06/03/08" Notional="10000000000" Index_Gearing="0" Margin="0.03" Cap="0" Floor="0" Spot_Factor="1" Spread="0" />

 

[John.Woody]

In my example Your Example
pc row = xNode.parentNode.nodeName
type VerifyUser = xattr.BaseName
"laptop" "summitftsuper" =xattr.Value


If you run the sub DisplayNode line by line you should be able to see what values are being pulled in and where, then by writing if statements you can decide what you want to do with the values.

 

[Darth Vodka]

In my example Your Example
pc row = xNode.parentNode.nodeName
type VerifyUser = xattr.BaseName
"laptop" "summitftsuper" =xattr.Value


If you run the sub DisplayNode line by line you should be able to see what values are being pulled in and where, then by writing if statements you can decide what you want to do with the values.

thanks

i can see how the obect works in VBA, that's much better

i actually think the file is rubbish from IT

unlinked tables cascaded about the place, next to useles

many thanks