• How to save XML data to database?

    Posted on June 19, 2012 by in Database, SQL Server

    Imagine having to insert a collection of objects into database. Making a database connection for each object is expensive. Instead it is sometimes advisable to send the whole data (in XML or some other format) to database and have it processed in the stored procedure.  This is what we are going to do in this article. We would take the serialized xml as an input to the stored procedure. In the stored procedure we parse the XML and populate the data into source tables.

    For our example, following are our source tables. We store basic computer information in Computer table and we store its components in Components table and their SubComponents are stored in SubComponents table.

    --CREATE SOURCE TABLES
    DECLARE @Computer TABLE
    (
    ComputerID INT IDENTITY(1,1), ManufacturerName	VARCHAR(100) NOT NULL,
    ModelNo	VARCHAR(100) NOT NULL, rowGUID	VARCHAR(100) NOT NULL
    );
    
    DECLARE @Component TABLE
    (
    ComponentID	INT IDENTITY(1,1), ComponentName  VARCHAR(200) NOT NULL,
    ComputerID	INT NOT NULL, rowGUID 	VARCHAR(100) NOT NULL
    );
    
    DECLARE @SubComponent TABLE
    (
    SubComponentID	INT IDENTITY(1,1), SubComponentName VARCHAR(200) NOT NULL,
    SubComponentPrice DECIMAL, ComponentID INT NOT NULL,
    rowGUID			VARCHAR(100) NOT NULL
    );
    

    I am using following xml as input. This is the XML I have prepared by serializing a collection of Computer objects.

    --INPUT XML (SIMILAR TO SERIALIZED XML FROM FRONT-END)
    DECLARE @ComputerXML		VARCHAR(MAX)
    SET @ComputerXML = '<ArrayOfComputer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Computer>
        <ManufacturerName>Dell</ManufacturerName>
        <ModelNo>ABC123</ModelNo>
        <Components>
          <Component>
            <ComponentName>DiskDrives</ComponentName>
            <SubComponents>
              <SubComponent>
                <SubComponentName>ST3160813AS ATA Device</SubComponentName>
                <SubComponentPrice>10</SubComponentPrice>
              </SubComponent>
            </SubComponents>
          </Component>
          <!-- *********** more data comes here -->
    </ArrayOfComputer>'
    

    We use sp_xml_preparedocument stored procedure to parse the XML string. It used MSXML parser to parse the input and provides the parsed input for consumption.

    EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @XML

    Since the primary source tables are used by application, we initially would like to store data into temporary tables and later move them to source tables.  We use OPENXML to query the xmldocument prepared by the sp_xml_preparedocument stored procedure. We store all the computers from the xml into a #tempcomputers temporary table. As you would notice in the below snapshot, Components element is not broken down into rows, it is stored in xml format itself. Since we don’t have a indentity column in tempComputers table, in order to maintain relations and join between temporary tables, we populate all rows in the #tempComputers with NEWID().

    SELECT NEWID() as ComputerGUID, ManufacturerName, ModelNo, Components
    	INTO #tempComputers
    	FROM OPENXML(@xmlDoc,'ArrayOfComputer/Computer',2) WITH 
    	(ManufacturerName VARCHAR(100), ModelNo VARCHAR(50), Components XML)
    

    Since we got stored all the data into our temp table, we don’t need the XML document anymore.We call sp_xml_removedocument to remove the internal representation of the XML document specified by the document handle and invalidates the document handle.

    EXEC sp_xml_removedocument @xmlDoc

    Now we query #tempComputers table for components information and insert into  # tempComponents. The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row. A nodes() method invocation with the query expression ‘Components/Component‘ would return a rowset with multiple  rows, each containing a logical copy of the original XML document, and with the context item set to one of the <Component> nodes. The resulting rows are then cross joined with # tempComputers for the final output. Even in this case, SubComponents elements are not broken down into rows, they are stored in xml format itself. We also perist ComputerGUID into #tempComponents as this is used later to fetch the ComputerID from @Computer table.

    SELECT NEWID() as ComponentGUID, COMP.ComputerGUID, 
    X.c.value('(ComponentName)[1]', 'VARCHAR(255)') as ComponentName,				
    X.c.query('(SubComponents)') as SubComponents
    	INTO #tempComponents
    	FROM #tempComputers COMP CROSS APPLY COMP.Components.nodes('Components/Component') X(c)
    

    Using nodes() function along with CROSS JOIN, we fetch all the SubComponents from #tempComponents table and stored in to  #tempSubComponents table. We also perist ComponentGUID into #tempSubComponents as this is used later to fetch the ComponentID from @Components table.

    SELECT NEWID() as SubComponentGUID, COMPONENTS.ComponentGUID, 
    X.c.value('(SubComponentName)[1]', 'VARCHAR(255)') as SubComponentName,				
    X.c.value('(SubComponentPrice)[1]', 'decimal') as SubComponentPrice
    INTO #tempSubComponents
    FROM #tempComponents COMPONENTS CROSS APPLY COMPONENTS.SubComponents.nodes('SubComponents/SubComponent') X(c)
    

    We finally move data from temporary tables to our source tables in one shot. We start with moving data from #tempComputers to @Computer table.

    INSERT INTO @Computer (ManufacturerName,ModelNo, rowGUID) SELECT C.ManufacturerName, ModelNo, ComputerGUID FROM #tempComputers c
    

    We move data from #tempComponents to @Component. @Component contains a foreign key relation to ComputerID column of @Computer.In order to get the ComputerID we join with @Computer using ComputerGUID which is nothing but the rowGUID of @Computer table.

    INSERT INTO @Component
    SELECT COMP.ComponentName, C.ComputerID, COMP.ComponentGUID FROM #tempComponents COMP
    INNER JOIN @Computer c ON C.rowGUID = COMP.ComputerGUID
    

    We move data from #tempSubComponents to @SubComponent.  @SubComponent table contains a foreign key relation to ComponentID column of @Component. In order to get the ComponentID we join #tempSubComponents with @Component using omponentGUID which is nothing but the rowGUID of @Component table.

    INSERT INTO @SubComponent
    SELECT SC.SubComponentName, SC.SubComponentPrice, C.ComponentID, SC.SubComponentGUID
    FROM #tempSubComponents SC INNER JOIN @Component c ON C.rowGUID = SC.ComponentGUID
    

    We finally delete all temporary tables.

    DROP TABLE #tempSubComponents
    DROP TABLE #tempComponents
    DROP TABLE #tempComputers
    

    Output:

    Complete SQL:

    --CREATE SOURCE TABLES
    DECLARE @Computer TABLE
    (
    ComputerID INT IDENTITY(1,1), ManufacturerName	VARCHAR(100) NOT NULL,
    ModelNo		VARCHAR(100) NOT NULL, rowGUID		VARCHAR(100) NOT NULL
    );
    
    DECLARE @Component TABLE
    (
    ComponentID		INT IDENTITY(1,1), ComponentName  VARCHAR(200) NOT NULL,
    ComputerID		INT NOT NULL, rowGUID 	VARCHAR(100) NOT NULL
    );
    
    DECLARE @SubComponent TABLE
    (
    SubComponentID		INT IDENTITY(1,1), SubComponentName	VARCHAR(200) NOT NULL,
    SubComponentPrice	DECIMAL, ComponentID INT NOT NULL,
    rowGUID				VARCHAR(100) NOT NULL
    );
    
    --INPUT XML (SIMILAR TO SERIALIZED XML FROM FRONT-END)
    DECLARE @ComputerXML		VARCHAR(MAX)
    SET @ComputerXML = '<ArrayOfComputer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Computer>
        <ManufacturerName>Dell</ManufacturerName>
        <ModelNo>ABC123</ModelNo>
        <Components>
          <Component>
            <ComponentName>DiskDrives</ComponentName>
            <SubComponents>
              <SubComponent>
                <SubComponentName>ST3160813AS ATA Device</SubComponentName>
                <SubComponentPrice>10</SubComponentPrice>
              </SubComponent>
            </SubComponents>
          </Component>
          <Component>
            <ComponentName>DisplayAdapter</ComponentName>
            <SubComponents>
              <SubComponent>
                <SubComponentName>Intel(R) G33/G31 Express Chipset Family</SubComponentName>
                <SubComponentPrice>10</SubComponentPrice>
              </SubComponent>
            </SubComponents>
          </Component>
        </Components>
      </Computer>
      <Computer>
        <ManufacturerName>Apple</ManufacturerName>
        <ModelNo>DCE567</ModelNo>
        <Components>
          <Component>
            <ComponentName>IDEController</ComponentName>
            <SubComponents>
              <SubComponent>
                <SubComponentName>ATA Channel 0</SubComponentName>
                <SubComponentPrice>30</SubComponentPrice>
              </SubComponent>
            </SubComponents>
            <SubComponents>
              <SubComponent>
                <SubComponentName>Intel(R) 82801GB/GR/GH (ICH7 Family) Serial ATA Storage Controller - 27C0</SubComponentName>
                <SubComponentPrice>50</SubComponentPrice>
              </SubComponent>
            </SubComponents>
          </Component>
          <Component>
            <ComponentName>COMPort</ComponentName>
            <SubComponents>
              <SubComponent>
                <SubComponentName>Communications Port (COM1)</SubComponentName>
                <SubComponentPrice>100</SubComponentPrice>
              </SubComponent>
              <SubComponent>
                <SubComponentName>Communications Port (COM2)</SubComponentName>
                <SubComponentPrice>100</SubComponentPrice>
              </SubComponent>
            </SubComponents>
          </Component>
        </Components>
      </Computer>
    </ArrayOfComputer>'
    
    DECLARE @XML XML, @xmlDoc INT
    
    --CONVERT IT TO XML DATATYPE
    SET @XML = CAST(convert(nvarchar(max),@ComputerXML) as XML)
    
    --Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), 
    --and provides the parsed document in a state ready for consumption. This parsed document is a tree representation 
    --of the various nodes in the XML document: --elements, attributes, text, comments, and so on.
    EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @XML
    
    --SINCE THE PRIMARY SOURCE TABLES ARE USED BY APPLICATION, WE INITIALLY WOULD LIKE TO STORE
    -- DATA INTO TEMPORARY TABLES AND LATER MOVE THEM TO SOURCE TABLES
    
    --QUERY THE XML DOCUMENT USING OPENXML
    -- WE STORE ALL THE COMPUTERS INTO A TEMPORARY TABLE. 
    -- Components are not broken down and are stored in xml format
    SELECT NEWID() as ComputerGUID, ManufacturerName, ModelNo, Components
    INTO #tempComputers
    FROM OPENXML(@xmlDoc,'ArrayOfComputer/Computer',2) WITH 
    	(ManufacturerName VARCHAR(100), ModelNo VARCHAR(50), 
    		 Components XML)	 		
    
    --Removes the internal representation of the XML document 
    --specified by the document handle and invalidates the document handle.
    EXEC sp_xml_removedocument @xmlDoc
    
    --NOW WE BROKE DOWN COMPONENTS FROM #tempComputers INTO INDIVIDUAL ROWS AND STORED IN #tempComponents 
    --THE RELATION BETWEEN COMPUTERS AND COMPONENTS IS MAINTAINED BY STORING ComputerGUID in tempComponents table
    -- Again SubComponents are not broken down and are saved in xml format
    SELECT NEWID() as ComponentGUID, COMP.ComputerGUID, 
    				X.c.value('(ComponentName)[1]', 'VARCHAR(255)') as ComponentName,				
    				X.c.query('(SubComponents)') as SubComponents
    	INTO #tempComponents
    	FROM #tempComputers	COMP CROSS APPLY COMP.Components.nodes('Components/Component') X(c) 	
    
    
    
    --NOW WE BROKE DOWN COMPONENTSTYPES FROM #tempComponents INTO INDIVIDUAL ROWS AND STORED IN #tempSubComponents 
    --THE RELATION BETWEEN COMPONENTS AND COMPONENTSTYPES IS MAINTAINED BY STORING ComponentGUID in tempSubComponents table
    SELECT NEWID() as SubComponentGUID, COMPONENTS.ComponentGUID, 
    				X.c.value('(SubComponentName)[1]', 'VARCHAR(255)') as SubComponentName,				
    				X.c.value('(SubComponentPrice)[1]', 'decimal') as SubComponentPrice
    INTO #tempSubComponents
    FROM #tempComponents COMPONENTS CROSS APPLY COMPONENTS.SubComponents.nodes('SubComponents/SubComponent') X(c) 		
    			
    --Now we move data from temp tables to source tables in one shot
    --We initially move data from #tempComputers to @Computer 
    INSERT INTO @Computer (ManufacturerName,ModelNo, rowGUID)
    SELECT C.ManufacturerName, ModelNo, ComputerGUID	
    FROM #tempComputers c
    	
    --We move data from #tempComponents to @Component 
    -- @Component contains a foreign key relation to ComputerID column of @Computer.
    -- In order to get the ComputerID we join with @Computer using ComputerGUID which is nothing but the rowGUID of @Computer table
    INSERT INTO @Component
    SELECT COMP.ComponentName, C.ComputerID, COMP.ComponentGUID
    FROM #tempComponents COMP
    INNER JOIN @Computer c ON C.rowGUID = COMP.ComputerGUID
    	
    --We move data from #tempSubComponents to @SubComponent 
    -- @SubComponent contains a foreign key relation to ComponentID column of @Component.
    -- In order to get the ComponentID we join #tempSubComponents with @Component using ComponentGUID 
    -- which is nothing but the rowGUID of @Component table
    INSERT INTO @SubComponent
    SELECT SC.SubComponentName, SC.SubComponentPrice, C.ComponentID, SC.SubComponentGUID
    FROM #tempSubComponents SC
    INNER JOIN @Component c ON C.rowGUID = SC.ComponentGUID
    		
    --DROP TEMPORARY TABLES
    DROP TABLE #tempSubComponents
    DROP TABLE #tempComponents
    DROP TABLE #tempComputers
    
    Be Sociable, Share!
      Post Tagged with ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    One Responseso far.

    1. Umashankar says:

      Thanks for this article. it helped me a lot.

    Leave a Reply