I want to bulk-insert XML data into SQL Server tables.

What have I achieved so far?

I have successfully loaded the XML file into SQL Server and fetched XML data.

What's the problem Now?

The retrieved XML data is not in the desired format. The column names are not in actual column format. Check the below screenshot for an understanding.

XML to SQL server error

XML file:

<?xml version="1.0" encoding="utf-8"?>
<bulkCmConfigDataFile>
    <fileHeader/>
    <configData>
        <class name="BTS5900">
             <object technique="SRAN" vendor="Huawei" version="BTS5900 V100R018C10SPC160">
                <class name="ALMCURCFG_BTS5900">
                    <object>
                        <parameter name="AID" value="29249"/>
                        <parameter name="ALVL" value="DEFAULT"/>
                        <parameter name="ASS" value="DEFAULT"/>
                        <parameter name="SHLDFLG" value="UNSHIELDED"/>
                    </object>
                    <object>
                        <parameter name="AID" value="65033"/>
                        <parameter name="ALVL" value="MAJOR"/>
                        <parameter name="ANM" value="Genset Operation"/>
                        <parameter name="ASS" value="ENV"/>
                        <parameter name="SHLDFLG" value="DEFAULT"/>
                    </object>
                    <object>
                        <parameter name="AID" value="65034"/>
                        <parameter name="ALVL" value="MAJOR"/>
                        <parameter name="ANM" value="High Temperature"/>
                        <parameter name="ASS" value="ENV"/>
                        <parameter name="SHLDFLG" value="DEFAULT"/>
                    </object>
                </class>
            </object>
        </class>
    </configData>
</bulkCmConfigDataFile>

Current result:

AID       29249
ALVL      DEFAULT
ASS       DEFAULT
SHLDFLG   UNSHIELDED
AID       65033
ALVL      MAJOR
ANM       Genset Operation
ASS       ENV
SHLDFLG   DEFAULT
AID       65034
ALVL      MAJOR
ANM       High Temperature
ASS       ENV
SHLDFLG   DEFAULT

I want to get the XML data into this format:

AID,   ALVL,     ANM,               ASS,       SHLDFLG
------------------------------------------------------
29249, DEFAULT,  NULL               DEFAULT,   UNSHIELDED
65033, MAJOR,    GENERAL OPERATION, ENV,       DEFAULT
65034, MAJOR,    HIGH TEMPERATURE,  ENV,       DEFAULT

There is One Condition: I don't know the names of the columns. My XML is a huge file that's why I can't add all XML content into the question.

This is my code:

DECLARE @xml xml
SELECT @xml = C FROM OPENROWSET (BULK 'E:\Cell_Sense\CM_Input\my_xml.xml', SINGLE_BLOB) AS Cars(C)   
DECLARE @hdoc int
    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

SELECT * 
FROM OPENXML (@hdoc, '/bulkCmConfigDataFile/configData/class/object/class/object/parameter') --'/bulkCmConfigDataFile/configData/class[@name="ALMCURCF"]/object/parameter'
WITH(
    name VARCHAR(100),
    value VARCHAR(100)
  ) 
EXEC sp_xml_removedocument @hdoc

I have successfully loaded the XML file into SQL Server and got all rowset results but the problem is that the fetched data is not in the desired format (I need to format this according to the Destination Table /View so that I can bulk insert it into my table).

CodePudding user response：

Try something like this:

DECLARE @xml xml
SELECT @xml = C FROM OPENROWSET (BULK 'E:\Cell_Sense\CM_Input\my_xml.xml', SINGLE_BLOB) AS Cars(C)   

SELECT
    AID = xc.value('(parameter[@name="AID"]/@value)[1]', 'varchar(20)'),
    ALVL = xc.value('(parameter[@name="ALVL"]/@value)[1]', 'varchar(20)'),
    ANM = xc.value('(parameter[@name="ANM"]/@value)[1]', 'varchar(50)'),
    ASS = xc.value('(parameter[@name="ASS"]/@value)[1]', 'varchar(50)'),
    SHLDFLG = xc.value('(parameter[@name="SHLDFLG"]/@value)[1]', 'varchar(50)')
FROM
    @xml.nodes('/bulkCmConfigDataFile/configData/class/object/class/object') AS XT(XC)

You should get a result something like this: