If current suggestions are not helping, some more context could help. At some degree you'll likely need to know what should be done with those data.
What is your intent ? You are trying to support user defined columns ? Or this is an attempt to ease later code changes by a developer ? Maybe built-in support for XML or JSON could help ?
If you are really using a table parameters you would need anyway to change the SQL Server side type definition ?
In my opinion, I think you should know the node name and the columns at least. Then, you could try to use the
OPENXML Rowset to generate the table.
OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
sample as below:
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
2 Points
8 Posts
XML to dynamic SQL table
Dec 12, 2018 10:57 AM|shwetasawant119|LINK
Is it possible to Convert XML to SQL table dynamically when XML tags are not known?
Contributor
7366 Points
1604 Posts
Re: XML to dynamic SQL table
Dec 12, 2018 11:02 AM|me_ritz|LINK
Possible solution suggested on your previous post...
https://forums.asp.net/t/2150302.aspx?Dynamic+User+define+table+type
All-Star
48570 Points
18079 Posts
Re: XML to dynamic SQL table
Dec 12, 2018 12:30 PM|PatriceSc|LINK
Hi,
If current suggestions are not helping, some more context could help. At some degree you'll likely need to know what should be done with those data.
What is your intent ? You are trying to support user defined columns ? Or this is an attempt to ease later code changes by a developer ? Maybe built-in support for XML or JSON could help ?
If you are really using a table parameters you would need anyway to change the SQL Server side type definition ?
All-Star
45489 Points
7008 Posts
Microsoft
Re: XML to dynamic SQL table
Dec 13, 2018 03:14 AM|Zhi Lv - MSFT|LINK
Hi Sir,
In my opinion, I think you should know the node name and the columns at least. Then, you could try to use the OPENXML Rowset to generate the table.
OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
sample as below:
DECLARE @idoc int, @doc varchar(1000); SET @doc =' <ROOT> <Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="42" Quantity="10"/> </Order> </Customer> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail ProductID="72" Quantity="3"/> </Order> </Customer> </ROOT>'; --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; -- SELECT stmt using OPENXML rowset provider SELECT * FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2) WITH (OrderID int '../@OrderID', CustomerID varchar(10) '../@CustomerID', OrderDate datetime '../@OrderDate', ProdID int '@ProductID', Qty int '@Quantity');The result:
Best regards,
Dillion