XML Convert to TABLE

Question

I have a table, contains an XML file in each row (column XMLRow). Each xml includes two parts, PartI and PartII. For each partI, we can have multiple PartII (SEQUENCE). I want to insert it into my database. The part one will be inserted into TableI and the second table for PartII is called TableII.

CREATE TABLE XMLDATA {
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
insertDate Datetime NOT NULL DEFAULT getdate(),
XMLRow XML NOT NULL
}

The below is an example of XMLRow in XMLDATA table,

The tables created as the following,

CREATE TABLE TABLEI {
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
messageNumber INT NOT NULL,
msgCnt INT NOT NULL
};
GO
CREATE TABLE TABLEII {
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
T1_id Not NULL REFERENCES TableI (id) ON DELETE CASCADE,
partII-Id INT NOT NULL,
partII-Value NVARCHAR(50) NOT NULL
};
GO

I have Identity Primary Keys in each table as there is no unique ID in the xml stored in the database.

DECLARE @docHandle INT
DECLARE @XML AS XML
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML 
SELECT @XML = XMLRow FROM [XMLDATA] 

INSERT INTO TableI (messageId, msgCnt)
SELECT messageNumber,  msgCnt       
FROM OPENXML (@docHandle, '/MessageFrame/value/BasicSafetyMessage/partI')  
WITH (
        messageNumber int '../../../messageId',
        msgCnt        int 'msgCnt',
    ) 

INSERT INTO TableII (T1_id, partII-Id, partII-Value)
SELECT @XML = XMLRow FROM [XMLDATA] 
SELECT T1_id,  partII-Id , partII-Value 
FROM OPENXML (@docHandle, '/MessageFrame/value/BasicSafetyMessage/PartII/SEQUENCE')  
WITH (
        T1_id              int @@IDENTITY, 
        partII-Id          int 'partII-Id',
        partII-Value       int 'partII-Value',
    ) 
EXEC sp_xml_removedocument @docHandle

The result should have been like this,

**Table1**
------------------------
id messageNumber msgCnt      
------------------------
1  20            127


**Table2**
-------------------------------
id T1_id partII-Id partII-Value
-------------------------------
1  1     0         BLUE
2  1     3         RED

The second INSERT INTO returns NULL. Can anyone help me with this please? Am I doing something wrong here?

Gottfried Lesigang · Accepted Answer

First of all: FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more. Rather use the appropriate methods the XML data type provides.

Your XML can easily be flattened with this query:

SELECT id
      ,XMLRow.value(N'(/MessageFrame/messageNumber/text())[1]',N'int')
      ,XMLRow.value(N'(/MessageFrame/value/BasicSafetyMessage/partI/msgCnt/text())[1]',N'int')
      ,p2.value(N'(partII-Id/text())[1]',N'int')
      ,p2.value(N'(partII-Value/text())[1]',N'nvarchar(max)')
FROM XMLDATA
CROSS APPLY XMLRow.nodes(N'/MessageFrame/value/BasicSafetyMessage/partII/SEQUENCE') AS A(p2);

But the following will provide you with an easy approach to get the cross-references on the fly. For this I add a column to TABLEI, hope this is possible for you:

CREATE TABLE XMLDATA  (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
insertDate Datetime NOT NULL DEFAULT getdate(),
XMLRow XML NOT NULL
);

CREATE TABLE TABLEI (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
XMLDATA_ID INT, --I add this column, to use the OUTPUT clause with INSERT
                --If this is no option for you, read this: 
                --https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted
messageNumber INT NOT NULL,
msgCnt INT NOT NULL
);

CREATE TABLE TABLEII (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
T1_id INT Not NULL REFERENCES TableI (id) ON DELETE CASCADE,
[partII-Id] INT NOT NULL,
[partII-Value] NVARCHAR(50) NOT NULL
);
GO

--I insert two XMLs to show the set-based principles:

INSERT INTO XMLDATA(XMLRow) VALUES
(N'
    20
    
        
            
                127
            
            
                
                    0
                    BLUE
                
                
                    3
                    RED
                
            
        
    
')
,(N'
    30
    
        
            
                300
            
            
                
                    10
                    BLACK
                
                
                    20
                    WHITE
                
            
        
    
');

--We need an interimistic table to get the IDENTITY based IDs

DECLARE @tblGetInsertedId TABLE(XMLDATA_ID INT, Id INT);

--We use the OUTPUT clause to puffer the id from XMLDATA with the newly given id in TABLEI:

INSERT INTO TableI(XMLDATA_ID, messageNumber, msgCnt)
OUTPUT inserted.XMLDATA_ID, inserted.id INTO @tblGetInsertedId(XMLDATA_ID, id)
SELECT id
      ,XMLRow.value(N'(/MessageFrame/messageNumber/text())[1]',N'int')
      ,XMLRow.value(N'(/MessageFrame/value/BasicSafetyMessage/partI/msgCnt/text())[1]',N'int')
FROM XMLDATA;

--We insert the rest into TABLEII, joining the id-puffer

INSERT INTO TABLEII(T1_id,[partII-Id], [partII-Value])
SELECT i.Id
      ,p2.value(N'(partII-Id/text())[1]',N'int')
      ,p2.value(N'(partII-Value/text())[1]',N'nvarchar(max)')
FROM XMLDATA xd
CROSS APPLY xd.XMLRow.nodes(N'/MessageFrame/value/BasicSafetyMessage/partII/SEQUENCE') AS A(p2)
LEFT JOIN @tblGetInsertedId i ON xd.id=i.XMLDATA_ID;

--Be happy with the result :-D

SELECT * FROM TABLEI
SELECT * FROM TABLEII

--Clean up (careful with real data!)

GO
/*
DROP TABLE TABLEII;
DROP TABLE TABLEI;
DROP TABLE XMLDATA;
*/

XML Convert to TABLE

Answers (2)

Related Questions