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,

<MessageFrame>
    <messageNumber>20</messageNumber>
    <value>
        <BasicSafetyMessage>
            <partI>
                <msgCnt>127</msgCnt>
            </partI>
            <partII>
                <SEQUENCE>
                    <partII-Id>0</partII-Id>
                    <partII-Value>BLUE</partII-Value>
                </SEQUENCE>
                <SEQUENCE>
                    <partII-Id>3</partII-Id>
                    <partII-Value>RED</partII-Value>
                </SEQUENCE>
            </partII>
        </BasicSafetyMessage>
    </value>
</MessageFrame>

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?

OT: Avoid *name numbering*. This is - in most cases - a sign for a bad design. Additionally avoid numbering with roman numbers. Use `Table2` instead of `TableII`. Compare the tables names in code, comment and text, Sometimes the `I` looks like a capital `i` or a small `L`... — Shnugo, May 26 '18 at 14:40

score 1 · Answer 1 · answered May 26 '18 at 05:37

Alright. So this will flatten out your XML:

DECLARE @XmlData XML = N'<MessageFrame>
    <messageNumber>20</messageNumber>
    <value>
        <BasicSafetyMessage>
            <partI>
                <msgCnt>127</msgCnt>
            </partI>
            <partII>
                <SEQUENCE>
                    <partII-Id>0</partII-Id>
                    <partII-Value>BLUE</partII-Value>
                </SEQUENCE>
                <SEQUENCE>
                    <partII-Id>3</partII-Id>
                    <partII-Value>RED</partII-Value>
                </SEQUENCE>
            </partII>
        </BasicSafetyMessage>
    </value>
</MessageFrame>';

SELECT
    N1.MessageFrame.value('(messageNumber)[1]', 'INT') AS MessageNumber
  , N3.partI.value('(msgCnt)[1]', 'INT') AS MessageCount
  , N5.Sequence.value('(partII-Id)[1]', 'INT') AS Id
  , N5.Sequence.value('(partII-Value)[1]', 'NVARCHAR(50)') AS Name
FROM @XmlData.nodes('MessageFrame') AS N1(MessageFrame)
CROSS APPLY N1.MessageFrame.nodes('value/BasicSafetyMessage') AS N2(MessageBasicSafety)
CROSS APPLY N2.MessageBasicSafety.nodes('partI') AS N3(partI)
CROSS APPLY N2.MessageBasicSafety.nodes('partII') AS N4(partII)
CROSS APPLY N4.partII.nodes('//partII/SEQUENCE') AS N5(Sequence);

And will give you the following result

MessageNumber MessageCount Id Name
------------- ------------ -- ---- 
20            127          0  BLUE 
20            127          3  RED

So now it's just a matter of putting everything to tables with correct references, which you totally can do.

As `` seems to be `1:1` related there's no need to use `.nodes()` for this. And the rest is just repeating the ``. One single `.nodes()` can do this. The `//` in `N5` is actually wrong. You are not diving deeper starting with `N4.partII`, but you are starting a deep search right from the top (You might do `.//partII`), but it was still needlessly complicated. — Shnugo, May 26 '18 at 14:36

score 1 · Accepted Answer · answered May 26 '18 at 14:33

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'<MessageFrame>
    <messageNumber>20</messageNumber>
    <value>
        <BasicSafetyMessage>
            <partI>
                <msgCnt>127</msgCnt>
            </partI>
            <partII>
                <SEQUENCE>
                    <partII-Id>0</partII-Id>
                    <partII-Value>BLUE</partII-Value>
                </SEQUENCE>
                <SEQUENCE>
                    <partII-Id>3</partII-Id>
                    <partII-Value>RED</partII-Value>
                </SEQUENCE>
            </partII>
        </BasicSafetyMessage>
    </value>
</MessageFrame>')
,(N'<MessageFrame>
    <messageNumber>30</messageNumber>
    <value>
        <BasicSafetyMessage>
            <partI>
                <msgCnt>300</msgCnt>
            </partI>
            <partII>
                <SEQUENCE>
                    <partII-Id>10</partII-Id>
                    <partII-Value>BLACK</partII-Value>
                </SEQUENCE>
                <SEQUENCE>
                    <partII-Id>20</partII-Id>
                    <partII-Value>WHITE</partII-Value>
                </SEQUENCE>
            </partII>
        </BasicSafetyMessage>
    </value>
</MessageFrame>');

--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;
*/

A top-notch answer, Thanks! – Mehrad Eslami May 26 '18 at 18:09 — Mehrad Eslami, May 26 '18 at 18:09

XML Convert to TABLE

2 Answers2