XML Source

T

Terry Holland

Im not sure if this is the correct group for this post so please advise of
any other managed newsgroup that would be more appropriate.

I am looking for a method of producing reports of data from a SQL Server
database. The database holds stock information. I am considering HTML,
Crystal and Excel as my reporting tool. Im not very familiar with Excel's
2003 XML capabilities so I'd like someone to advise me whether I can do what
I want to do. Im also not very experienced with XML.

Many of our stock items are assemblies of other stock items and some are
standalone stock items. I have included a sample of XML that could be
produced.
What Id like to know is whether it's possible to set up a template in Excel
that I could use to open my XML export and have it display the top-level
stock items differently to the assmebly sub-items. ie Have the top-level
stock items dispayed with a light blue background and have the text bold,
and have any sub-items of that top level stock item displayed underneath it
with yellow background and italicised text.

Is this something that could easily be set up using Excel 2003

Regards

Terry Holland

=================================
Example of Excel Display
=================================

Stock Code Description Quantity Cost Price Sale Price
000100 Item A £100.00 £200.00
000101 Item A: Subitem 1 4 £10.00 £20.00
000102 Item A: Subitem 2 2 £11.00 £22.00
000103 Item A: Subitem 3 3 £8.00 £16.00
000200 Item B £523.00 £1,046.00
000201 Item B: Subitem 1 2 £52.00 £104.00
000202 Item B: Subitem 2 4 £58.00 £116.00
000203 Item B: Subitem 3 4 £18.00 £36.00
000300 Item C £265.00 £530.00
000400 Item D £15.00 £30.00


=================================
End of Example of Excel Display
=================================



=================================
XML
=================================
<?xml version="1.0" encoding="utf-8" ?>
<PriceList>
<StockItem>
<StockCode>000100</StockCode>
<Description>Item A</Description>
<CostPrice>100.00</CostPrice>
<SalePrice>200.00</SalePrice>
<StockItem>
<StockCode>000101</StockCode>
<Description>Item A: Subitem 1</Description>
<CostPrice>10.00</CostPrice>
<SalePrice>20.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000102</StockCode>
<Description>Item A: Subitem 2</Description>
<CostPrice>11.00</CostPrice>
<SalePrice>22.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000103</StockCode>
<Description>Item A: Subitem 3</Description>
<CostPrice>8.00</CostPrice>
<SalePrice>16.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000200</StockCode>
<Description>Item B</Description>
<CostPrice>523.00</CostPrice>
<SalePrice>1046.00</SalePrice>
<StockItem>
<StockCode>000201</StockCode>
<Description>Item B: Subitem 1</Description>
<CostPrice>52.00</CostPrice>
<SalePrice>104.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000202</StockCode>
<Description>Item B: Subitem 2</Description>
<CostPrice>58.00</CostPrice>
<SalePrice>116.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000203</StockCode>
<Description>Item B: Subitem 3</Description>
<CostPrice>18.00</CostPrice>
<SalePrice>36.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000300</StockCode>
<Description>Item C</Description>
<CostPrice>265.00</CostPrice>
<SalePrice>530.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000400</StockCode>
<Description>Item D</Description>
<CostPrice>15.00</CostPrice>
<SalePrice>30.00</SalePrice>
</StockItem>
</PriceList>

=================================
End of XML
=================================
 
G

Guest

why use xml? can't you just populate a recordset through an ADO connection?

you can then use the Range CopyFromRecordset method to load the sheet.
 
T

Terry Holland

1) I want sub-items clearly identified by using a different formatting.
2) I use XML throughout the application as a means of transferring data.
3) I want to see what Excel 2003 can do with XML
 
P

Peter Huang [MSFT]

Hi

So far I am researching the issue, and I will update you with new
information ASAP.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
N

NickHK

Terry,
What about using a linked table in Access and using its report.
You can export to HTML.

NickHK
 
T

Terry Holland

So it looks like I will not easily be able to use Excel for my reporting
needs. I'm a little surprised as Im sure this is quite a common reporting
scenario.
Would it make any difference if by sub-item had a different element name ie
<SubItem> instead of <StockItem>?


"Peter Huang" said:
Hi

Based on my research, the Excel sheet is an two dimension table.
But your test xml file is of three dimension.
e.g.
To Excel, the data below have no difference.
000100 Item A ?00.00 ?00.00
000101 Item A: Subitem 1 4 ?0.00 ?0.00

Here is what we can done so far.
1. [Test.xsd, Schema File]
<?xml version="1.0" encoding="utf-8" >
<xs:schema id="PriceList" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema" >
<xs:element name="PriceList">
<xs:complexType>
<xs:sequence>
<xs:element name="StockItem">
<xs:complexType>
<xs:sequence>
<xs:element name="StockCode" type="xs:string" minOccurs="0" />
<xs:element name="Description" type="xs:string" minOccurs="0" />
<xs:element name="CostPrice" type="xs:string" minOccurs="0" />
<xs:element name="SalePrice" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>


2. Attached xlt file which has been formatted, and it will format the data
based on the column.

3. Double click the Test.xlt, and it will open a new workbook. import the
data file(the content you have post in your first post) via the steps below.
3.1 select menu Data/XML/Import

Hope this helps.

BTW: even if we attach a schema conform to your orginal data file, Excel
can only attach one of the root node in the schema as below.
Because the scheme below is of three dimensions.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="PriceList" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="StockItem">
<xs:complexType>
<xs:sequence>
<xs:element name="StockCode" type="xs:string" minOccurs="0" />
<xs:element name="Description" type="xs:string" minOccurs="0" />
<xs:element name="CostPrice" type="xs:string" minOccurs="0" />
<xs:element name="SalePrice" type="xs:string" minOccurs="0" />
<xs:element ref="StockItem" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="PriceList" msdata:IsDataSet="true"
msdata:Locale="zh-CN">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="StockItem" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
Click to expand...
rights.
 
P

Peter Huang [MSFT]

Hi

As the schema, we can consider PriceList as a table, StockItem as a
tablerow and StockCode... as a column.
If you wants to add more nodes, e.g. <SubItem>, I think you may try to add
them a new column, so it will apprear as a new column in the excel table.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Top