Xml data import weirdness

[Donal McWeeney]

Hi,

When trying to export xml from a web url we have noticed some really weird
behaviour.

The xml is off the following format:

<Items>
<Item>
<Sample1></Sample1>
<Sample2></Sample2>
</Item>
<Item>
<Sample1></Sample1>
<Sample2></Sample2>
</Item>

etc


However if you try and import it you get the first two rows as:

/Items
/Item/Sample1 /Item/Sample2




We stumbled across a workaround for the /Items problem by changing the Items
element definition to the following:

<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

The import then will just import the data - any ideas why adding this
namespace seemed to fix the problem?

Excel also appears to ignore any inline schema we specify in the import
xml - is it possible to specify an inline schema? The setting up a schema
map is not the most end user friendly approach for us to take.


However using the above workaround we have stumbled across another problem
when trying to import from a really really long url. Firstly the url is too
long to paste so you have to navigate to it using the "New Web Query"
browser. The xml is displayed in the "New Web Query" browser, but when you
select the data source the displayed in excel contains the /Items and
/Item/etc rows described above. Also when this problem happens the data is
not included in a table!

Any idea why this may be?

Thanks

Donal

 

[Guest]

XML stands for X Meta Language. Meta laguage has format information that is
contained in angle brackets <format>. In a webbrowser you can see this format
by going to view Menu - Source. You can also save the XML file to your disk
and look at the file with notepad.

It is not unusal for one program to save data diffferently from another
program reading the same data. It may be better to copy the section of the
XML file you want using Edit - Copy and then pasting Text only inot excel.

 

[Chip Pearson]

XML stands for X Meta Language. Meta laguage has format information that

is
contained in angle brackets <format>.

That is completely wrong. XML standard eXtensible Markup Language. But yes,
it does have a lot of < > characters.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Donal McWeeney]

Hi Joel,

Thanks for the response but I've been working with XML for the last many
years and the problems I am trying to solve is the inconsistency of how
Excel behaves when importing xml from a web query.

Why does it handle the data differently with the xsi namespace declaration?
Why does it not handle inline schemas?
Why does it not handle the data when it comes in from a long url?

Thanks

Donal

 

[Wei Lu [MSFT]]

Hello Donal,

Have you tried to use the Import XML directly instead of using the web
query?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Donal McWeeney]

Hi Wei,

Yes we tried that but the Import XML has to be a file - not a url so it is
of no use to use.

But if we do import the file using Import XML it does work.

Thanks

Donal

 

[Wei Lu [MSFT]]

Hello Donal,

I could import the xml from the web like using the url :
http://sha-offsvr-01:7070/Docs/Test/data.xml

And I could get the same result of a XML in local machine.

Could you have a try?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Donal McWeeney]

Hi Wei,

Could you mail me the data.xml file - I cant get at the url you sent.

I can import from a short url like you have specified - however if the url
has more that 252 or 256 characters the xml being imported is not processed
correctly.

Thanks

Donal

 

[Wei Lu [MSFT]]

Hello Donal,

The XML file I use is the one you have send to me. I just upload it to my
MOSS test environment.

Could you please let me know the url you have issue?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.