Problem in Importing XML data to access 2003-7 database (1 Viewer)

harshgupta · Feb 10, 2010

Hi,

I am facing a problem regarding importing data from [COLOR=blue! important][COLOR=blue! important]XML[/COLOR][/COLOR] to access database.

Part of Sample XML:-

<Credit
<CreditDt>2011-11-13</CreditDt>
<CreditSCd>AC</CreditCd>
<NumericValue>
<FormatText id="AADEE">string</FormatText>
</NumericValue>
<SecondaryCd>Shot</SecondaryCd>
</Credit>
<Credit>
<CreditDt id="AAGFC">1979-10-19</CreditDt>
<CreditCd>AC</CreditCd>
<NumericValue id="AAGFD">
<FormatInteger>-5638</FormatInteger>
</NumericValue>
<SecondaryCd>CR</SecondaryCd>
</Credit>

Now when i am importing data into access [COLOR=blue! important][COLOR=blue! important]database[/COLOR][/COLOR] it is forming the table NumaricValue with first column as FormatText but when this node is again occuring with different child node FormatInteger then this colummn is not getting appended in the table.

Which is leading to loss of data for the column FormatInteger as this column is not formed.

Please suggest any solution for the same

gemma-the-husky · Feb 10, 2010

Just a thought

the problem may be these bits embedded within the XML - these are attributes id="AAGFD", and actually are only semi-visible to a XML parser. OPen a XML browser to look at the XML - you probably wont see these at all immediately.

Maybe your import is ignoring them altogether

-----------
I wrote some stuff to parse a XML file by wlaking the nodes - and getting at these attributes was far from easy!

harshgupta · Feb 11, 2010

gemma-the-husky said:
Just a thought

the problem may be these bits embedded within the XML - these are attributes id="AAGFD", and actually are only semi-visible to a XML parser. OPen a XML browser to look at the XML - you probably wont see these at all immediately.

Maybe your import is ignoring them altogether

-----------
I wrote some stuff to parse a XML file by wlaking the nodes - and getting at these attributes was far from easy!

Hi,

We have tried with removing attributes id="AAGFD", still we are facing the same problem.

gemma-the-husky · Feb 11, 2010

sorry, i see again

the first record has a tag called formattext, and the data IS text
the second record has a tag called formatinteger, and the data IS numeric

both the actual values are hidden in the attrubute

the records are subtly different in other ways

the second record has an attribute in the date tag
i presume the tag creditSCD in the first record is a typo, as there is no equivalent closing tag

what are you trying to do, exactly?

how are you importing the data?

harshgupta · Feb 15, 2010

gemma-the-husky said:
sorry, i see again

the first record has a tag called formattext, and the data IS text
the second record has a tag called formatinteger, and the data IS numeric

both the actual values are hidden in the attrubute

the records are subtly different in other ways

the second record has an attribute in the date tag
i presume the tag creditSCD in the first record is a typo, as there is no equivalent closing tag

what are you trying to do, exactly?

how are you importing the data?

Hi,

I want to export all the data from XML to the Access database and then have to verify that whether data imported is loaded successfully without any fail.

I am using import wizard to load the data from XML to access database.

I am getting the in the following format: In which Numeric Value is the table name and Format Text is one column.

Numeric Value:

FormatText

But it should make the following table: In which Numeric value is the table and FoarmatText and FormatInteger should be the columns.

Numeric Value:

FormatText FormatInteger

gemma-the-husky · Feb 15, 2010

i dont think this is correct

"format text" and "format integer" are both separate/independent tags within the tag "numeric value"

but i dont think a xml import will retrieve that entire tag, as a value for the outside tag "numeric value"

is that what you are asking?

-------------
it seems to me that its a variant record

for some records the tag numeric value contains text, and others its a value

harshgupta · Feb 18, 2010

gemma-the-husky said:
i dont think this is correct

"format text" and "format integer" are both separate/independent tags within the tag "numeric value"

but i dont think a xml import will retrieve that entire tag, as a value for the outside tag "numeric value"

is that what you are asking?

-------------
it seems to me that its a variant record

for some records the tag numeric value contains text, and others its a value

Take this XML part

<NumericValue>
<FormatText id="AADEE">string</FormatText>
<FormatInteger>-5638</FormatInteger>
</NumericValue>

When i am importing this to access database i am getting

NumericValue as table with columns FormatText and FormatInteger as columns.

Then why this XML part

<NumericValue>
<FormatText id="AADEE">string</FormatText>
</NumericValue>
'
'
'
<NumericValue id="AAGFD">
<FormatInteger>-5638</FormatInteger>
</NumericValue>

Is giviving me NumericValue as table and FormatText as only column.

Why the column FormatInteger and its correponding values are getting Lost during the import ?

gemma-the-husky · Feb 18, 2010

just a surmise

maybe when the xml import looks at the firsat record it "sees" the tag format text only.

since it rthen doesnt realize there IS a variant tag "format integer" it igonres it completely in the import.

-------------
tbh. when I tried to use xml import it didnt make any sense at all - it just created tables of all the tags it found, but didnt relate the tables together with Pk/FK - so it wasnt any use anyway. I assumed it would do this, as it "knows " the parent of any node.

I gave up and "rolled my own" import - I think the answer lies in style sheets/transforms (XLSTs) but i couldnt see a working example of these, and havent got to grips with them yet.

gemma-the-husky · Feb 18, 2010

just a surmise

maybe - when the xml import looks at the first record it "sees" the tag format text only.

since it then doesnt realize there IS a variant tag "format integer" it ignores that tag completely in the import - just a conjecture, you understand

-------------
tbh. when I tried to use xml import it didnt make any sense at all - it just created tables of all the tags it found, but didnt relate the tables together with Pk/FK - so it wasnt any use anyway. I assumed it would do this, as it "knows " the parent of any node.

I gave up and "rolled my own" import - I think the answer lies in style sheets/transforms (XLSTs) but i couldnt see a working example of these, and havent got to grips with them yet.

Problem in Importing XML data to access 2003-7 database (1 Viewer)

Has anyone faced this problem

yes

no

harshgupta

New member

gemma-the-husky

Super Moderator

harshgupta

New member

gemma-the-husky

Super Moderator

harshgupta

New member

gemma-the-husky

Super Moderator

harshgupta

New member

gemma-the-husky

Super Moderator

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread