locked
XML to dynamic SQL table RRS feed

  • Question

  • User-1554906967 posted

    Is it possible to Convert XML to SQL table dynamically when XML tags are not known?

    Wednesday, December 12, 2018 10:57 AM

All replies

  • User-2146987983 posted

    Possible solution suggested on your previous post...

    https://forums.asp.net/t/2150302.aspx?Dynamic+User+define+table+type

    Wednesday, December 12, 2018 11:02 AM
  • User753101303 posted

    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 ?

    Wednesday, December 12, 2018 12:30 PM
  • User-271186128 posted

    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:

    OrderID CustomerID           OrderDate                 ProdID    Qty  
    ------------------------------------------------------------------------  
    10248      VINET       1996-07-04 00:00:00.000   11      12  
    10248      VINET       1996-07-04 00:00:00.000   42      10  
    10283      LILAS       1996-08-16 00:00:00.000   72      3  

    Best regards,
    Dillion

    Thursday, December 13, 2018 3:14 AM