Convert Xml to Table SQL Server

This is the answer, hope it helps someone :)

First there are two variations on how the xml can be written:

1

Answer:

SELECT  
       Tbl.Col.value('IdInvernadero[1]', 'smallint'),  
       Tbl.Col.value('IdProducto[1]', 'smallint'),  
       Tbl.Col.value('IdCaracteristica1[1]', 'smallint'),
       Tbl.Col.value('IdCaracteristica2[1]', 'smallint'),
       Tbl.Col.value('Cantidad[1]', 'int'),
       Tbl.Col.value('Folio[1]', 'varchar(7)')
FROM   @xml.nodes('//row') Tbl(Col)

2.

Answer:

SELECT  
       Tbl.Col.value('@IdInvernadero', 'smallint'),  
       Tbl.Col.value('@IdProducto', 'smallint'),  
       Tbl.Col.value('@IdCaracteristica1', 'smallint'),
       Tbl.Col.value('@IdCaracteristica2', 'smallint'),
       Tbl.Col.value('@Cantidad', 'int'),
       Tbl.Col.value('@Folio', 'varchar(7)')

FROM   @xml.nodes('//row') Tbl(Col)

Taken from:

http://kennyshu.blogspot.com/2007/12/convert-xml-file-to-table-in-sql-2005.html
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

Converting XML data to SQL table

Sorry, in my first attempt I completely misread your question and thought you'd like to get the data out of your XML. This is the approach to create such an XML out of table's data:

DECLARE @cust TABLE(ID INT, CustomerName VARCHAR(100),DateOfBirth DATE);
INSERT INTO @cust VALUES(1,'Jonny','1990-01-02T00:00:00')
                       ,(2,'Jimmy','1980-01-02T00:00:00');

DECLARE @ord TABLE(ID INT,CustomerID INT,OrderDate DATE, OrderAmount INT);
INSERT INTO @ord VALUES(1,1,'1993-02-03T00:00:00',1000)
                      ,(2,1,'1994-02-03T00:00:00',500)
                      ,(3,2,'1994-02-03T00:00:00',200);

SELECT c.CustomerName AS [@Name]
      ,c.DateOfBirth AS [@DateofBirth]
      ,(
        SELECT o.OrderDate
              ,o.OrderAmount
        FROM @ord AS o
        WHERE o.CustomerID=c.ID
        FOR XML PATH('OrderInfo'),TYPE
       )
FROM @cust AS c
FOR XML PATH('Customer'),ROOT('SalesDetails')

And this is the created XML

Just for the case you want to read your XML, I let this appended

You can retrieve all the information like this:

The generated Index columns are IDs you can use to insert this into relational tables. The problem with your XML is, that the information about your target tabels is missing. But the rest should be easy for you.

Btw: I declared some more similar nodes to make the relational structure visible

DECLARE @x XML=
'
  
    
      1993-02-03T00:00:00
      1000
    
    
      1994-02-03T00:00:00
      500
    
  
  
    
      1994-02-03T00:00:00
      200
    
    
      1993-02-03T00:00:00
      100
    
  
';

WITH CustomerNodes AS
(
     SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS CustomerIndex
           ,Customer.value('@Name','varchar(max)') AS CustomerName
           ,Customer.value('@DateofBirth','date') AS CustomerDateOfBirth
           ,One.Customer.query('.') AS CustomerNode
     FROM @x.nodes('SalesDetails/Customer') AS One(Customer)
)
SELECT cn.*
      ,ROW_NUMBER() OVER(PARTITION BY cn.CustomerIndex ORDER BY (SELECT NULL)) AS OrderIndex
      ,OrderInfo.value('OrderDate[1]','date') AS OrderDate
      ,OrderInfo.value('OrderAmount[1]','int') AS OrderAmount
 FROM CustomerNodes AS cn
 CROSS APPLY cn.CustomerNode.nodes('Customer/OrderInfo') As The(OrderInfo)

The result:

Customer                 Order
ID   Name    DateOfBirth ID  OrderDate   OrderAmount
1   Johny   1990-01-02  1   1993-02-03  1000
1   Johny   1990-01-02  2   1994-02-03  500
2   Jimmy   1980-01-02  1   1994-02-03  200
2   Jimmy   1980-01-02  2   1993-02-03  100

SQL Query to convert XML Data to Columns in Table

Please try the following solution.

It is a minimal reproducible example. Just copy it as-is to SSMS and run it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (page_id INT, entry_id INT, page_xml XML);
INSERT INTO @tbl (page_id, entry_id, page_xml) VALUES
(2219, 1996, N'
  
    pg1_UIObject1
    
    StlAnnotationControl
    
  
  
    pg1_TabItem1Panel1
    
    StlCanvas
    
  
  
    pg1_txtPrefix
    MS
    StlTextBox
    
  
  
    pg1_txtSurname
    Joey
    StlTextBox
    
  
  
    pg1_txtNO
    400232
    StlTextBox
    
  
');

DECLARE @targetTbl TABLE (page_id INT, entry_id INT, N VARCHAR(30), V VARCHAR(30), T VARCHAR(30), A VARCHAR(30));
-- DDL and sample data population, end

WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/FormPersistance.xsd')
INSERT INTO @targetTbl (page_id, entry_id, N, V, T, A) 
SELECT page_id, entry_id, 
   c.value('(N/text())[1]', 'NVARCHAR(30)') AS N,
   c.value('(V/text())[1]', 'NVARCHAR(30)') AS V,
   c.value('(T/text())[1]', 'NVARCHAR(30)') AS T,
   c.value('(A/text())[1]', 'NVARCHAR(30)') AS A
FROM @tbl
    CROSS APPLY page_xml.nodes('/ArrayOfControl/Control') AS t(c);

-- test
SELECT * FROM @targetTbl;

Output

+---------+----------+--------------------+--------+----------------------+------+
| page_id | entry_id |         N          |   V    |          T           |  A   |
+---------+----------+--------------------+--------+----------------------+------+
|    2219 |     1996 | pg1_UIObject1      | NULL   | StlAnnotationControl | NULL |
|    2219 |     1996 | pg1_TabItem1Panel1 | NULL   | StlCanvas            | NULL |
|    2219 |     1996 | pg1_txtPrefix      | MS     | StlTextBox           | NULL |
|    2219 |     1996 | pg1_txtSurname     | Joey   | StlTextBox           | NULL |
|    2219 |     1996 | pg1_txtNO          | 400232 | StlTextBox           | NULL |
+---------+----------+--------------------+--------+----------------------+------+

How to convert XML data into a SQL Server table

Try this:

DECLARE @Data XML = '
    
        Harj Dhamrait
        13
        
            
                454854 5532281
                TelephoneNumber
            
            
                0987262 532281
                Other
                Switchboard
            
            
                abc@gmail.com
                EmailAddress
            
            
                01322 296 252
                FaxNumber
            
        
    
'

SELECT
    TelephoneNumber = xc.value('(ContactNumber[ContactNumberTypeCoded="TelephoneNumber"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    Switchboard = xc.value('(ContactNumber[ContactNumberTypeCodedOther="Switchboard"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    EmailAddress = xc.value('(ContactNumber[ContactNumberTypeCoded="EmailAddress"]/ContactNumberValue/text())[1]', 'varchar(50)'),
    FaxNumber = xc.value('(ContactNumber[ContactNumberTypeCoded="FaxNumber"]/ContactNumberValue/text())[1]', 'varchar(50)')
FROM
    @Data.nodes('/OrderContact/Contact/ListOfContactNumber') AS XT(XC)

You should get the desired output:

Sample Image

The .nodes() method call returns a XML fragment representing the node. You need to reach into that XML fragment, and extract each child node - based on what value they have in ContactNumberTypeCoded - and then show the value as the desired output.

SQL Server : convert XML data onto table

This is - using XML methods - much easier!

Try this:

DECLARE @xml XML='';

SELECT One.Attr.value('fn:local-name(.)','varchar(max)') AS field
      ,One.Attr.value('.','varchar(max)') AS data
FROM @xml.nodes('table_result/@*') AS One(Attr)

The result

field     data
id        001
subj_cd   cdaaa
grade     b
name      Phua Chu Kang

Now I try to imitate your table structure (I'd recommend to store the data as XML from the beginning! In this case you could omit the first CROSS APPLY with the CAST ... AS XML):

DECLARE @tbl TABLE(name VARCHAR(10),data VARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('a','') 
,('b','') 
,('c',''); 

SELECT tbl.name
      ,One.Attr.value('fn:local-name(..)','varchar(max)') AS element
      ,One.Attr.value('fn:local-name(.)','varchar(max)') AS field
      ,One.Attr.value('.','varchar(max)') AS data
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(tbl.data AS XML)) AS MyData(AsXml)
CROSS APPLY MyData.AsXml.nodes('*/@*') AS One(Attr)

The result

name  element      field    data
a     table_result  id      001
a     table_result  subj_cd cdaaa
a     table_result  grade   b
a     table_result  name    Phua Chu Kang
b     Another       test    test data
b     Another       test2   test2 data
c     OneMore       x       x data
c     OneMore       y       y data
c     OneMore       z       z data

How to convert xml as a table or html table in sqlserver

You find a much enhanced version of this answer here.

old answer:

because I don't know the column name when it pass as parameters

This makes it impossible, to call something like SELECT * FROM... You might think about dynamic SQL, but there is a great alternative: FLWOR

declare @tv  table
(
  id int, 
  username varchar(50),
  department varchar(50) 

)
insert into @tv values(1,'tom','finance'),(2,'mark','business');

SELECT
(
    SELECT *
    FROM @tv 
    FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
         return 
           {
             for $td in $tr/*
             return {$td/text()}
           }
                ')
FOR XML PATH('table')

The .query() will run through your XML and re-create it as demanded.

The result:












      1     tom     finance   
      2     mark     business

##UPDATE
##This is a solution with a FUNCTION on XML-base using FLWOR

It will transform any SELECT into a XHTML table: The call is as easy as this:

SELECT dbo.CreateHTMLTable((SELECT TOP 5 * FROM sys.objects FOR XML RAW,ELEMENTS XSINIL));

That's the code

CREATE FUNCTION dbo.CreateHTMLTable(@SelectForXmlRawElementsXsinil XML)
RETURNS XML
AS
BEGIN

RETURN
(
    SELECT  
    @SelectForXmlRawElementsXsinil.query('let $first:=/row[1]
                return 
                 
                {
                for $th in $first/*
                return {local-name($th)}
                }
                ') AS thead
    ,@SelectForXmlRawElementsXsinil.query('for $tr in /row
                 return 
                 
                 {
                 for $td in $tr/*
                 return {string($td)}
                 }
                 ') AS tbody
    FOR XML PATH('table'),TYPE
);
END
GO

--a mock-up-table with data

declare @tv  table
(
  id int, 
  username varchar(50),
  department varchar(50) 
)
--NULL value in row=2!!!
insert into @tv values(1,'tom','finance'),(2,NULL,'business');

--That's the way you use it

SELECT dbo.CreateHTMLTable((SELECT * FROM @tv FOR XML RAW,ELEMENTS XSINIL));

--Clean-Up

DROP FUNCTION dbo.CreateHTMLTable;

returns be aware of the NULL value in last row!




















            id       username       department     
  
            1       tom       finance     
          2       
      business

id	username	department
1	tom	finance
2		business

##UPDATE Possible Enhancements

You can control the layout easily via CSS
You might pass in class names for table, thead, tbody... for better CSS-control
One could pass in a one-row-footer with aggregated values as second parameter and append it as

Convert XML column to Table Data in SQL Query

Is this XML under your control?

I'm especially irritated by this

This line is redefining the default namespace.

The XML you show consists of a schema portion and a data portion

DECLARE @MyXML XML = 
'


    

    
    
        
        
            
            
                
                
                
            
            
        
        
    
    

    
    

    
    
        
        testing1
        
    
    

    

';

Your statement

Then, I would be able to insert the data into a table in sql.

... let's me think, that the real-world use case will consist of more than one column. In this case you do not show enough.

This query would read just the content in a lazy approach:

SELECT @MyXML.value('(//*:Col1/text())[1]','nvarchar(max)');

In general it is good to be as specific as possible, but the actual issue looks, as if you might be better off with a namespace wildcard and a deep search (//) down to :

SELECT de.query('.')
FROM @MyXml.nodes('//DocumentElement') A(de)

This query will return various content of this area:

SELECT de.value('(Blah/@*:id)[1]','nvarchar(max)') DiffGr_ID
      ,de.value('(Blah/@*:rowOrder)[1]','int') MsData_RowOrder
      ,de.value('(Blah/@*:hasChanges)[1]','nvarchar(max)') diffgr_HasChanges
      ,de.value('(Blah/Col1/text())[1]','nvarchar(max)') Col1_Text
FROM @MyXml.nodes('//DocumentElement') A(de);

The result

DiffGr_ID   MsData_RowOrder diffgr_HasChanges   Col1_Text
-----------------------------------------------------------
Blah1       0               inserted            testing1

Convert XML to SQL Server table

I would recommend to use the built-in XQuery support in SQL Server instead of the old, rather clunky OPENXML code.

Also, you are not respecting the default XML namespace that is defined on your XML document:


                    ***************************

You need to take that into account!

Try this code here (and you can extend it to return all the individual bits and pieces of your XML node):

WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/')
SELECT
    Codigo = XC.value('(Codigo)[1]', 'int'),
    Valor = XC.value('(Valor)[1]', 'varchar(20)'),
    PrazoEntrega = XC.value('(PrazoEntrega)[1]', 'int')
    -- add more expressions like the above for the other sub-elements
FROM
    @XML.nodes('/cResultado/Servicos/cServico') AS XT(XC)

Convert Xml to Table SQL Server