|
Home > Archive > MS SQL XML > October 2005 > Using OPENXML to get data from elements and attributes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Using OPENXML to get data from elements and attributes
|
|
| Craig HB 2005-10-27, 9:25 am |
| I am trying to convert XML into a table format using OPENXML. It seems simple
if your data is element-centric or attribute-centric, but I want to get data
from both elements and attributes.
What is the T-SQL (using OPENXML) that would convert the following into a
table format, using all the fields (elements and attributes)...
<InvoiceItem>
<ItemCode>262621</ItemCode>
<InvoiceQuantity ItemSize="EA">12</InvoiceQuantity>
<UnitPrice Amount="GBP">20.19</UnitPrice>
</InvoiceItem>
Thanks,
Craig
| |
| Adam Machanic 2005-10-27, 9:25 am |
| Here's one way:
declare @idoc int
declare @doc varchar(1000)
set @doc ='<InvoiceItem>
<ItemCode>262621</ItemCode>
<InvoiceQuantity ItemSize="EA">12</InvoiceQuantity>
<UnitPrice Amount="GBP">20.19</UnitPrice>
</InvoiceItem>'
exec sp_xml_preparedocume
nt @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/InvoiceItem', 1)
WITH (
ItemCode int 'ItemCode',
Quantity int 'InvoiceQuantity',
ItemSize char(2) 'InvoiceQuantity/@ItemSize',
Price decimal(9,2) 'UnitPrice',
MonetaryUnit char(3) 'UnitPrice/@Amount'
)
exec sp_xml_removedocumen
t @idoc
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Craig HB" <CraigHB@discussions.microsoft.com> wrote in message
news:08A00C87-35B5-437E-8DBE- 58C466FD3DED@microso
ft.com...
>I am trying to convert XML into a table format using OPENXML. It seems
>simple
> if your data is element-centric or attribute-centric, but I want to get
> data
> from both elements and attributes.
>
> What is the T-SQL (using OPENXML) that would convert the following into a
> table format, using all the fields (elements and attributes)...
>
> <InvoiceItem>
> <ItemCode>262621</ItemCode>
> <InvoiceQuantity ItemSize="EA">12</InvoiceQuantity>
> <UnitPrice Amount="GBP">20.19</UnitPrice>
> </InvoiceItem>
>
> Thanks,
> Craig
| |
| Craig HB 2005-10-27, 9:25 am |
| That's it, Adam -- it worked ! Thanks !!!
- Craig
|
|
|
|
|