Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
podgehb
Starting Member
18 Posts |
 Posted - 2005-10-11 : 12:58:34
|
| 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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-11 : 13:38:23
|
| I'm surprised that SQL Server cares ...Can you post some DDL for the table you want to insert into and I'll take a look.Kristen |
 |
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-11 : 16:12:46
|
| try this way, it should work:SELECT *FROM OPENXML (@idoc, '/ROOT/InvoiceItem')EXEC sp_xml_removedocument @idoc |
|
|
|
podgehb
Starting Member
18 Posts |
Posted - 2005-10-11 : 18:43:22
|
| This should make it clearer. From the XML above, I want to get this result...ItemCode - InvoiceQuantity - ItemSize - UnitPrice - Amount262621 - 12 - EA - 20.19 - GBP |
|
|
|
podgehb
Starting Member
18 Posts |
Posted - 2005-10-12 : 03:44:49
|
| Got the answer. Here it is: http://msdn.microsoft.com/news...437e-8dbe-58c466fd3ded |
|
|
|
|
|
|
Using OPENXML to get data from elements and attrib