How to send dynamic table in SQL

step 1.

CREATE TABLE [CUSTOMERS_TABLE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DOCUMENT] [varchar](20) NOT NULL,
[NAME] [varchar](50) NOT NULL,
[ADDRESS] [varchar](50) NOT NULL,
[PROFESSION] [varchar](50) NOT NULL,
CONSTRAINT [CUSTOMERS_PK] PRIMARY KEY ([Id])
)

step 2.

"1.0" encoding="utf-8"?>
000 000 000
Mary Angel
Your City, YC 1212
Systems Analyst
000 000 001
John Lenon
Your City, YC 1212
Driver
000 000 002
Alice Freeman
Your City, YC 1212
Architect
000 000 003
George Sands
Your City, YC 1212
Doctor
000 000 004
Mark Oliver
Your City, YC 1212
Writer

step 3.

INSERT INTO CUSTOMERS_TABLE (DOCUMENT, NAME, ADDRESS, PROFESSION)
SELECT
MY_XML.Customer.query('Document').value('.', 'VARCHAR(20)'),
MY_XML.Customer.query('Name').value('.', 'VARCHAR(50)'),
MY_XML.Customer.query('Address').value('.', 'VARCHAR(50)'),
MY_XML.Customer.query('Profession').value('.', 'VARCHAR(50)')
FROM (SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'C:\temp\MSSQLTIPS_XML.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('Customers/Customer') AS MY_XML (Customer);

if you want to create dynamic table based on XML data then you need to use dynamic sql as

declare @XML xml = '
000 000 000
Mary Angel
Your City, YC 1212
Systems Analyst
000 000 001
John Lenon
Your City, YC 1212
Driver
000 000 002
Alice Freeman
Your City, YC 1212
Architect
000 000 003
George Sands
Your City, YC 1212
Doctor
000 000 004
Mark Oliver
Your City, YC 1212
Writer
'
declare @SQL nvarchar(max) = ''
declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]'
select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
from @XML.nodes('/Customers/Customer[1]/*') as T(N)
set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''/Customers/Customer'') as T(N)'
exec sp_executesql @SQL, N'@XML xml', @XML

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
step 1.
CREATE TABLE [CUSTOMERS_TABLE](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DOCUMENT] [varchar](20) NOT NULL,
    [NAME] [varchar](50) NOT NULL,
    [ADDRESS] [varchar](50) NOT NULL,
    [PROFESSION] [varchar](50) NOT NULL,
CONSTRAINT [CUSTOMERS_PK] PRIMARY KEY ([Id])
)
step 2.
"1.0" encoding="utf-8"?>


    000 000 000
    Mary Angel

Your City, YC 1212

    Systems Analyst


    000 000 001
    John Lenon

Your City, YC 1212

    Driver


    000 000 002
    Alice Freeman

Your City, YC 1212

    Architect


    000 000 003
    George Sands

Your City, YC 1212

    Doctor


    000 000 004
    Mark Oliver

Your City, YC 1212

    Writer
step 3.
INSERT INTO CUSTOMERS_TABLE (DOCUMENT, NAME, ADDRESS, PROFESSION)
SELECT
   MY_XML.Customer.query('Document').value('.', 'VARCHAR(20)'),
   MY_XML.Customer.query('Name').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Address').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Profession').value('.', 'VARCHAR(50)')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\temp\MSSQLTIPS_XML.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('Customers/Customer') AS MY_XML (Customer);
if you want to create dynamic table based on XML data then you need to use dynamic sql as
declare @XML xml = '

000 000 000
Mary Angel
Your City, YC 1212

Systems Analyst


000 000 001
John Lenon
Your City, YC 1212

Driver


000 000 002
Alice Freeman
Your City, YC 1212

Architect


000 000 003
George Sands
Your City, YC 1212

Doctor


000 000 004
Mark Oliver
Your City, YC 1212

Writer


'

declare @SQL nvarchar(max) = ''
declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]'

select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
from @XML.nodes('/Customers/Customer[1]/*') as T(N)

set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''/Customers/Customer'') as T(N)'

exec sp_executesql @SQL, N'@XML xml', @XML