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');
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
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
44900 Points
16026 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
45469 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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.