How to Flatten ?

Is there an easy to understand method of bringing this into a flat structure
(all elements related to a line item on one line) ? I do not care if the
xsl is "optimal" with respect to efficiency, I just want it to work with as
little complexity as possible. Please note the multiple line items. I
have seen and understand examples where there is a one to one relationship
between the header and the detail line, but not when there are 3 levels
involved and multiple detail lines.
Thanks,
Rob
<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:%KB7d.14101$yp.4743@.bignews1.bellsouth.net...
> Is there an easy to understand method of bringing this into a flat
structure
> (all elements related to a line item on one line) ? I do not care if
the
Rob,
Does this work for you:
DECLARE @.xml varchar(2000)
set @.xml =
'<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc output, @.xml
SELECT * FROM OPENXML(@.hdoc, '/Invoices/Invoice/InvoiceItems/InvoiceItem',
2)
WITH (
InvoiceId char(3) '../../@.id',
Supplier varchar(20) '../../Supplier',
line char(1) '@.line',
Code1 char(3) 'Codes/Code1',
Code2 char(3) 'Codes/Code2',
NetAmount numeric(9,2) 'NetAmount')
EXEC sp_xml_removedocument @.hDoc
sql