Identify node and tag in XML data using T-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Identify node and tag in XML data using T-sql...

Identify node and tag in XML data using T-sql

Rate Topic

Display Mode

Topic Options

Author

Message

unnati.patel513

Posted Friday, March 22, 2013 2:12 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:47 PM
Points: 19, Visits: 230

Hi,

I have XML data store in Sql table. XML structure will not be static.i want write the T-sql Code to identify the Node using T-sql.

Below is the example.

<Sub>
<ID>1</ID>
<Name>Saran</Name>
<Address>123 Usa</Address>
<Contact>
<Address1>4534 China</Address1>
<Address2>7674 India</Address2>
</Contact>

</sub>

I want to write the Dynamic T-Sql code to pass the Node and Tag as parameter and load it into table.

Here i have two table one is for Sub and another is for contact.
I can not hardcode every thing because our XML are very huge contain 100 tables inside one XML.
Please help me on this how i identify the node and tag. How i will so this dynamically.

Thanks in Advance

Post #1434510

Steven Willis

Posted Friday, March 22, 2013 3:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 298, Visits: 1,320


DECLARE
   @strXML NVARCHAR(4000)
  ,@XML XML
   
IF OBJECT_ID('tempdb..#Sub') IS NOT NULL 
DROP TABLE #Sub

IF OBJECT_ID('tempdb..#Contact') IS NOT NULL 
DROP TABLE #Contact

CREATE TABLE #Sub (
   ID INT IDENTITY(1,1) NOT NULL,
   SubID INT NOT NULL,
   SubName NVARCHAR(50) NULL,
   SubAddress NVARCHAR(50) NULL,
   PRIMARY KEY (ID,SubID))
   
CREATE TABLE #Contact (
   ID INT IDENTITY(1,1) NOT NULL,
   SubID INT NOT NULL,
   ContactAddress1 NVARCHAR(50) NULL,
   ContactAddress2 NVARCHAR(50) NULL,
   PRIMARY KEY (ID,SubID))

SET @strXML = '
<Sub>
<ID>1</ID> 
<Name>Saran</Name>
<Address>123 Usa</Address>
<Contact>
<Address1>4534 China</Address1>
<Address2>7674 India</Address2>
</Contact>
</Sub>
<Sub>
<ID>2</ID> 
<Name>Rajesh</Name>
<Address>456 India</Address>
<Contact>
<Address1>321 Japan</Address1>
<Address2>987 Korea</Address2>
</Contact>
</Sub>
'


BEGIN TRY

   SELECT @XML = CONVERT(XML,@strXML)

   INSERT INTO #Sub   
   SELECT 
       x.value('(ID)[1]','int') AS SubID
      ,x.value('(Name)[1]','varchar(30)') AS SubName 
      ,x.value('(Address)[1]','varchar(30)') AS SubAddress 
   FROM @XML.nodes('/Sub') n(x)

   INSERT INTO #Contact   
   SELECT 
       x.value('(ID)[1]','int') AS SubID
      ,x.value('(Contact/Address1)[1]','varchar(30)') AS ContactAddress1 
      ,x.value('(Contact/Address2)[1]','varchar(30)') AS ContactAddress2
   FROM @XML.nodes('/Sub') n(x)

   SELECT 
      s.SubID
     ,s.SubName
     ,s.SubAddress
     ,c.ContactAddress1 
     ,c.ContactAddress2
   FROM 
      #Sub s
   INNER JOIN
      #Contact c
      ON s.SubID = c.SubID
   ORDER BY
      s.SubID

END TRY
BEGIN CATCH
   PRINT 'INVALID XML!'
END CATCH

Output

SubID	SubName	SubAddress	ContactAddress1	ContactAddress2
1	Saran	123 Usa		4534 China	7674 India
2	Rajesh	456 India	321 Japan	987 Korea

Post #1434521

unnati.patel513

Posted Friday, March 22, 2013 5:14 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:47 PM
Points: 19, Visits: 230

Thanks Steven Willis

I have another questions. if my XML files contain so many Node around 100. i need to load into more then 100 tables and each table contain more then 150 field so in this situation.i need check the each node and like <Contact>,<Sub> and load the data into the staging tables.

I am not sure how i will do that without hardcoding every thing.

Please give me some idea how i will do this.

Thansk a lot for your help.

Post #1434551

Steven Willis

Posted Saturday, March 23, 2013 12:59 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 298, Visits: 1,320

Try this procedure. You may have to make some modifications to fit your XML structure. And what you do with the output is up to you.


CREATE PROCEDURE dbo.ParseXML
    
    @strXML NVARCHAR(MAX)        
   ,@schemanode NVARCHAR(255)
   ,@rootnode NVARCHAR(255)    

AS
BEGIN

    SET NOCOUNT ON

    DECLARE
        @idoc INT
       ,@id INT
       ,@cpid INT
       ,@SoapEnvOpen NVARCHAR(MAX)
       ,@SoapEnvClose NVARCHAR(MAX)
       ,@SoapBodyOpen NVARCHAR(MAX)
       ,@SoapBodyClose NVARCHAR(MAX)
       ,@SchemaNodeOpen NVARCHAR(MAX)
       ,@SchemaNodeClose NVARCHAR(MAX)
       ,@checkSoap INT
       ,@isSoap BIT = 0   
       ,@checkSchema INT
       ,@isSchema BIT = 0 
       ,@checkXMLHeader INT
       ,@isHeader BIT = 0 
       ,@XMLHeader NVARCHAR(MAX)
       ,@debug BIT = 0
       

    IF OBJECT_ID('tempdb..#TreeList') IS NOT NULL
    DROP TABLE #TreeList

    IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
    DROP TABLE #NodeList

    IF OBJECT_ID('tempdb..#OutputList') IS NOT NULL
    DROP TABLE #OutputList

    SET @id = 1
    SET @cpid = NULL

                
    /* Check to see if any XML exists */
    
    SET @strXML = NULLIF(@strXML,'')
    
    IF @strXML IS NULL
        BEGIN
        
            SELECT
                NULL AS RowNum
               ,NULL AS id
               ,NULL AS parentid
               ,NULL AS nodepath
               ,NULL AS nodetype
               ,NULL AS nodename
               ,NULL AS property
               ,NULL AS value
               ,NULL AS nodecontents
               ,'No XML to process' AS XMLStatus                
            
            RETURN
            
        END 
    ELSE
        BEGIN
        
            -- Get rid of tabs and extra spaces    

            SET @strXML = REPLACE(@strXML,CHAR(9),'')
            SET @strXML = REPLACE(@strXML,CHAR(10),'')
            SET @strXML = REPLACE(@strXML,CHAR(13),'')

            IF CHARINDEX('<',@strXML,1) > 0
                BEGIN

                    SET @strXML = REPLACE(@strXML,'<','<')
                    SET @strXML = REPLACE(@strXML,'>','>')
                
                    IF @debug = 1
                        SELECT @strXML AS ReplacedXML
                END
                                        
            SET @strXML = 
            REPLACE(
                REPLACE(
                        REPLACE(
                            REPLACE(
                                @strXML
                            ,'  ',' '+CHAR(7))
                        ,CHAR(7)+' ','')
                    ,CHAR(7),'')
                ,'> <','><')
    
        END
        
    IF @debug = 1
        SELECT @strXML AS InputXML


    /* Check to see if the XML has a header */
    
    SET @checkXMLHeader = CHARINDEX('<?xml version',@strXML,0)

    IF @checkXMLHeader > 0
        SET @isHeader = 1


    /* If the XML has a header then remove it */

    IF @isHeader = 1
        BEGIN        
            SET @XMLHeader = SUBSTRING(@strXML,CHARINDEX('<?xml version',@strXML,0),CHARINDEX('>',@strXML,0))
            SET @strXML = REPLACE(@strXML,@XMLHeader,'')
        END
        
    IF @debug = 1
        SELECT @XMLHeader AS XMLHeader
        

    /* Check to see if the XML has a SOAP wrapper */
    
    SET @checkSoap = CHARINDEX('<soapenv:Envelope',@strXML,0)

    IF @checkSoap > 0
        SET @isSoap = 1


    /* If the XML has a SOAP wrapper then remove it */

    IF @isSoap = 1
        BEGIN        
            SET @SoapEnvOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Envelope',@strXML,0),CHARINDEX('>',@strXML,0))
            SET @strXML = REPLACE(@strXML,@SoapEnvOpen,'')
            SET @SoapBodyOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Body',@strXML,0),CHARINDEX('>',@strXML,0))
            SET @strXML = REPLACE(@strXML,@SoapBodyOpen,'')
            SET @SoapEnvClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Envelope>',@strXML,0),LEN('</soapenv:Envelope>'))
            SET @strXML = REPLACE(@strXML,@SoapEnvClose,'')
            SET @SoapBodyClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Body>',@strXML,0),LEN('</soapenv:Body>'))
            SET @strXML = REPLACE(@strXML,@SoapBodyClose,'')
        END


    IF @debug = 1
        BEGIN
            SELECT @SoapEnvOpen AS Soap_Wrapper
            SELECT @SoapBodyOpen AS Soap_Body
            SELECT @strXML AS XML_Without_SoapWrapper
        END


    /* Check to see if the XML has a schema definition node */
    
    SET @checkSchema = CHARINDEX('<'+@schemanode,@strXML,0)

    IF @checkSchema > 0
        SET @isSchema = 1
    

    /* If a schema definition node exists remove it */
    
    IF @isSchema = 1
        BEGIN
    
            SET @SchemaNodeOpen = SUBSTRING(@strXML,CHARINDEX('<'+@schemanode,@strXML,0),CHARINDEX('>',@strXML,0))
            SET @strXML = REPLACE(@strXML,@SchemaNodeOpen,'')
            SET @SchemaNodeClose = SUBSTRING(@strXML,CHARINDEX('</'+@schemanode+'>',@strXML,0),LEN('</'+@schemanode+'>'))
            SET @strXML = REPLACE(@strXML,@SchemaNodeClose,'')

        END
        

    BEGIN TRY
        EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
    END TRY
    BEGIN CATCH
        BEGIN
            
            IF @debug = 1
                BEGIN
                    SELECT @strXML AS ModifiedXML
                    SELECT CONVERT(XML,@strXML) AS FormattedXML        
                END
            
            SELECT
                NULL AS RowNum
               ,NULL AS id
               ,NULL AS parentid
               ,NULL AS nodepath
               ,NULL AS nodetype
               ,NULL AS nodename
               ,NULL AS property
               ,NULL AS value
               ,NULL AS nodecontents
               ,'Invalid XML' AS XMLStatus    

            RETURN        
            
        END
    END CATCH

    ;WITH TreeList (cpid,cid)
    AS 
    (
        SELECT 
             CAST(p1.parentid AS INT) AS cpid
            ,CAST(p1.id AS INT) AS cid
        FROM
            OPENXML (@idoc,@rootnode,2) AS p1

        UNION ALL
        
        SELECT 
             CAST(p2.parentid AS INT) AS cpid
            ,CAST(p2.id AS INT) AS cid
        FROM
            OPENXML (@idoc,@rootnode,2) AS p2
        JOIN 
            TreeList
            ON CAST(TreeList.cid AS INT) = CAST(p2.parentid AS INT)
        WHERE
            CAST(p2.parentid AS INT) = @cpid  
    ),
    NodeList (nid,npid,nodetype,localname,[text])    
    AS
    (
        SELECT 
             xmllist.id AS nid 
            ,xmllist.parentid AS npid
            ,xmllist.nodetype
            ,xmllist.localname
            ,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
        FROM TreeList
        INNER JOIN 
            OPENXML (@idoc,@rootnode,2) AS xmllist
            ON TreeList.cid = xmllist.id  
    )
    SELECT  
         IDENTITY(INT,1,1) AS tRow
        ,t.cid
        ,t.cpid 
        ,n.nid
        ,n.npid
        ,n.nodetype
        ,n.localname
        ,n.[text]
    INTO #TreeList
    FROM 
        TreeList t
    INNER JOIN
        NodeList n
        ON n.npid = t.cid
        
    --SELECT 
    --*
    --FROM 
    --    #TreeList
           

    ;WITH RecursiveNodes(tRow,id,parentid,nodepath,localname,[text],nodetype)
    AS (
        SELECT
             #TreeList.tRow
            ,#TreeList.nid AS id
            ,#TreeList.npid AS parentid
            ,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#TreeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
            ,#TreeList.localname
            ,CAST(#TreeList.[text] AS NVARCHAR(MAX)) AS [text]
            ,0 AS nodetype
        FROM 
            #TreeList
        WHERE
            #TreeList.cpid IS NULL
            
        UNION ALL

        SELECT
             n.tRow
            ,n.nid AS id
            ,n.npid AS parentid
            ,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
            ,n.localname
            ,n.[text]
            ,n.nodetype
        FROM #TreeList AS n
        INNER JOIN
            RecursiveNodes AS r
            ON r.id = n.npid
    )
    SELECT
        ROW_NUMBER() OVER (ORDER BY Result.id) AS RowNum
       ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY Result.id) AS nodeid
       ,Result.nodepath
       ,Result.nodename
       ,Result.nodecontents
    INTO #OutputList
    FROM
        (
        SELECT
            rn.tRow
           ,rn.id
           ,rn.parentid
           ,rn.nodepath
           ,(CASE
                WHEN rn.nodetype = 0 THEN 'Root'
                WHEN rn.nodetype = 1 THEN 'Node'
                WHEN rn.nodetype = 2 THEN 'Property'
                ELSE 'Data'
             END) AS nodetype
           ,(CASE
                WHEN rn.nodetype = 0 THEN rn.localname
                WHEN rn.nodetype = 1 THEN rn.localname
                WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
                ELSE NULL
             END) AS nodename
           ,ISNULL((CASE
                WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
                WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
                ELSE NULL
             END),'') AS nodecontents    
        FROM
            RecursiveNodes AS rn

        ) AS Result
    WHERE
        nodetype <> 'Root'
        AND nodename IS NOT NULL
    ORDER BY
        nodeid
        ,RowNum

    SELECT * FROM #OutputList

END

Now run it with your sample data. Notice I added a telephone number node that is commented out.
Remove the comments and see that the nodes are parsed dynamically.


	EXEC dbo.ParseXML 
	'<Root>
	<Sub>
	<ID>1</ID> 
	<Name>Saran</Name>
	<!--Telephone>123-456-7890</Telephone-->
	<Address>123 Usa</Address>
	<Contact>
	<Address1>4534 China</Address1>
	<Address2>7674 India</Address2>
	</Contact>
	</Sub>
	<Sub>
	<ID>2</ID> 
	<Name>Rajesh</Name>
	<!--Telephone>987-654-3210</Telephone-->
	<Address>456 India</Address>
	<Contact>
	<Address1>321 Japan</Address1>
	<Address2>987 Korea</Address2>
	</Contact>
	</Sub>
	</Root>'
	,NULL
	,'/Root'

Post #1434576

unnati.patel513

Posted Saturday, March 30, 2013 7:51 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:47 PM
Points: 19, Visits: 230

Thanks Steven Willis.

That is what i was looking for. it was awesome logic.

I have another question.

Once i identify the node and tag i need to load the data into normalized staging table dynamically

EXEC dbo.ParseXML
'<Root>
<Application>
<ApplicantEntryComplete>false</ApplicantEntryComplete>
<AssistantUnderwriter>davisg</AssistantUnderwriter>
<Brokerage>
<AddressLine1>5605 Glenridge Dr NE</AddressLine1>
<AddressLine2>One Premier Plaza, Ste 300</AddressLine2>
<Contact>
<PhoneNumber>5605 Glenridge Dr NE</PhoneNumber>
<Mobile>One Premier Plaza, Ste 300</Mobile>
</Contact>
</Brokerage>
<InsuredCompany>
<AddressLine1>5605 Glenridge Dr NE</AddressLine1>
<AddressLine2>One Premier Plaza, Ste 300</AddressLine2>
<Assets>0</Assets>
<BusinessDesc>Broker/Consultant</BusinessDesc>
<Contact>
<PhoneNumber>5605 Glenridge Dr NE</PhoneNumber>
<Mobile>One Premier Plaza, Ste 300</Mobile>
</Contact>
</InsuredCompany>
</Application>
</Root>'
,NULL
,'/Root'

I have created metadata table to identify the Node to staging table mapping(I have created staging table based on Node)
Below is script for the staging tables.

Create table dbo.Stg_Application
(
Appl_ID Int Identity(1,1),
ApplicantEntryComplete Varchar(50) ,
AssistantUnderwriter Varchar(50)
)

Create table dbo.Stg_Company
(
Company_ID Int Identity(1,1),
Appl_ID int,
AddressLine1 Varchar(50),
AddressLine2 Varchar(50)
)

Create table dbo.Stg_Contact
(
Cont_ID Int Identity(1,1),
Company_ID int ,
PhoneNumber Varchar(50) ,
Mobile varchar(50)
)

and i have created metadata tables to identify the node to staging table mapping
Below is script for metadata tables

Create table Dbo.NodeToStaging
(
Node Varchar(500),
StagingTable Varchar(500)
)

Insert into Dbo.NodeToStaging(Node,StagingTable)
values('Application','dbo.Stg_Application'),
('Brokerage','dbo.Stg_Company'),
('InsuredCompany','dbo.Stg_Company'),
('Contact','dbo.Stg_Contact')

Please help me how i will do this.
I am not very good in dynamic code.

Note : staging table contain more then 100 columns but in XML may contain 5 or 10 attributes.
and name of columns in XML and name the column in staging is same.and staging tables has parent child relationship logically.

Many thanks in Advanced.

Post #1437264

Steven Willis

Posted Monday, April 01, 2013 12:14 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 298, Visits: 1,320

As you inferred, I needed to use dynamic SQL to get the results you want. From your example I wasn't able to get the XML nodes and your tables to match up exactly so I took the liberty of renaming a few items. You said this was just a small sample anyway so it should work for you once you match everything up with the real data and columns. I tried not to hardcode any more than necessary. I'm sure with some work you could probably replace the hardcoded values with variables easily enough.

So we start with a (very slightly) modified version of your XML.


EXEC dbo.ParseXMLNodeToStaging
'<Root>
<Application>
<ID>1</ID>
<ApplicantEntryComplete>false</ApplicantEntryComplete>
<AssistantUnderwriter>davisg</AssistantUnderwriter>
<Brokerage>Gamble Underwriters
<AddressLine1>5605 Glenridge Dr NE</AddressLine1>
<AddressLine2>One Premier Plaza, Ste 300</AddressLine2>
<Contact>
<ContactName>Jeremy Irons</ContactName>
<PhoneNumber>555-123-4567</PhoneNumber>
<Mobile>555-456-7890</Mobile>
</Contact>
</Brokerage>
<InsuredCompany>
<CompanyName>USA Corp</CompanyName>
<AddressLine1>1100 Pennsylvania Ave</AddressLine1>
<AddressLine2>White House Plaza, West Wing</AddressLine2>
<Assets>0</Assets>
<BusinessDesc>Government</BusinessDesc>
<Contact>
<ContactName>Melinda Bates</ContactName>
<PhoneNumber>123-456-7890</PhoneNumber>
<Mobile>123-456-7899</Mobile>
</Contact>
</InsuredCompany>
</Application>
<Application>
<ID>2</ID>
<ApplicantEntryComplete>false</ApplicantEntryComplete>
<AssistantUnderwriter>johnd</AssistantUnderwriter>
<Brokerage>Amelia Brothers
<AddressLine1>1105 Independence Blvd</AddressLine1>
<AddressLine2></AddressLine2>
<Contact>
<ContactName>Mary Johnson</ContactName>
<PhoneNumber>704-678-1345</PhoneNumber>
<Mobile>704-789-0909</Mobile>
</Contact>
</Brokerage>
<InsuredCompany>
<CompanyName>LittleTown Industries</CompanyName>
<AddressLine1>123 Main St</AddressLine1>
<AddressLine2>Apt 1</AddressLine2>
<Assets>100</Assets>
<BusinessDesc>IT</BusinessDesc>
<Contact>
<ContactName>Terrence OBrien</ContactName>
<PhoneNumber>899-345-1234</PhoneNumber>
<Mobile>899-640-9876</Mobile>
</Contact>
</InsuredCompany>
</Application>
</Root>'
,NULL
,'/Root'

Before executing the procedure we must create the base tables (I used temp tables but you will of course use your own tables.


	/* These would be your permanent tables */

	IF OBJECT_ID('tempdb..#Stg_Application') IS NULL
		BEGIN
			Create table #Stg_Application
			( 
			Appl_ID Int Identity(100,1) NOT NULL,
			ApplicantEntryComplete	Varchar(50),
			AssistantUnderwriter	Varchar(50),
			PRIMARY KEY (Appl_ID)	
			)
		END
		
	IF OBJECT_ID('tempdb..#Stg_Company') IS NULL
		BEGIN
			Create table #Stg_Company
			(	
			Company_ID Int Identity(1000,10) NOT NULL,
			Appl_ID	                Int,
			BrokerageName           Varchar(50),
			BrokerageAddressLine1	Varchar(50),
			BrokerageAddressLine2	Varchar(50),
			BrokerageContactName    Varchar(50),
			BrokeragePhoneNumber	Varchar(50),
			BrokerageMobile	        Varchar(50),
			CompanyName             Varchar(50),
			Assets                  Int,
			BusinessDesc            Varchar(50),
			AddressLine1            Varchar(50),
			AddressLine2            Varchar(50),
			PRIMARY KEY (Company_ID)
			)
		END

	IF OBJECT_ID('tempdb..#Stg_Contact') IS NULL
		BEGIN
			Create table #Stg_Contact
			(	
			Cont_ID Int Identity(10000,10) NOT NULL,
			Company_ID  Int,
			ContactName Varchar(50),
			PhoneNumber	Varchar(50),
			Mobile	    Varchar(50),
			PRIMARY KEY (Cont_ID)
			)
		END
		
--for cleanup while testing		
--drop table #Stg_Application
--drop table #Stg_Company
--drop table #Stg_Contact

Finally, here's the code for a procedure to insert the data into the proper tables.


CREATE PROCEDURE [dbo].[ParseXMLNodeToStaging]
    
    @strXML NVARCHAR(MAX)        
   ,@schemanode NVARCHAR(255)
   ,@rootnode NVARCHAR(255)    

AS
BEGIN

    SET NOCOUNT ON

    IF OBJECT_ID('tempdb..#NodeToStaging') IS NOT NULL
        DROP TABLE #NodeToStaging
        
        Create table #NodeToStaging    
        (
        ID     Int    Identity(1,1) NOT NULL,
        Node         Varchar(500),
        StagingTable Varchar(500),
        PRIMARY KEY (ID)
        )

        Insert into #NodeToStaging(Node,StagingTable)
        values
        ('Application','#Stg_Application'),
        ('Company','#Stg_Company'),
        ('Contact','#Stg_Contact')


    /* Another table to hold the XML results */

    IF OBJECT_ID('tempdb..#XML_Output') IS NOT NULL
        DROP TABLE #XML_Output

        Create table #XML_Output
        ( 
        ID    Int    Identity(1,1),
        RowNum int,
        nodeid int,
        nodepath nvarchar(255),
        nodename nvarchar(255),
        nodecontents nvarchar(255),
        PRIMARY KEY (ID,RowNum)
        )

    
    /* A variable to hold the dynamic insert statements */
    
    DECLARE @strSQL NVARCHAR(MAX)
    SET @strSQL = ''


    /* Parse the XML */
    
    INSERT INTO #XML_Output
    EXEC dbo.ParseXML
        @strXML
       ,@schemanode
       ,@rootnode    
       
    
    /* Query the XML output and organize it */
    
    ;WITH 
    cteApplication
    AS
    (
        SELECT
             x.ID
            ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID
            ,1 AS nodetype
            ,'Application' AS nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid
            ,nodename           
            ,nodecontents
            ,s.StagingTable
        FROM
            #XML_Output x
        INNER JOIN
            #NodeToStaging s
            ON s.Node = 'Application'
        WHERE
            nodename IN ('ApplicantEntryComplete','AssistantUnderwriter')
    ),
    cteCompany
    AS
    (
        SELECT
             x.ID
            ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID
            ,2 AS nodetype
            ,'Company' AS nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid
            ,(CASE
                WHEN nodename = 'Brokerage' THEN 'BrokerageName'
                WHEN nodepath LIKE '/Application/Brokerage%' THEN 'Brokerage'+nodename
                ELSE nodename
              END) AS nodename
            ,nodecontents
            ,s.StagingTable
        FROM
            #XML_Output x
        INNER JOIN
            #NodeToStaging s
            ON s.Node = 'Company'
        WHERE
            nodepath LIKE '%Application/%'
            AND nodename NOT IN ('ApplicantEntryComplete','AssistantUnderwriter')
            AND nodepath NOT LIKE '%InsuredCompany/Contact%'

    ),
    cteContact
    AS
    (
        SELECT
             x.ID
            ,ROW_NUMBER() OVER (PARTITION BY nodepath ORDER BY x.ID) AS Appl_ID
            ,3 AS nodetype
            ,'Contact' AS nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodename ORDER BY x.ID) AS nodeid
            ,nodename           
            ,nodecontents
            ,s.StagingTable
        FROM
            #XML_Output x
        INNER JOIN
            #NodeToStaging s
            ON s.Node = 'Contact'
        WHERE
            nodepath LIKE '%/InsuredCompany/Contact%'
            AND nodename IN ('ContactName','PhoneNumber','Mobile')
    )
    SELECT
        @strSQL = @strSQL + 
        'INSERT INTO '+r.StagingTable+' ('+
         (CASE nodedesc
            WHEN 'Application' THEN
                 STUFF((SELECT ',['+nodename+']' AS [text()] 
                 FROM cteApplication
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')
            WHEN 'Company' THEN
                 '[Appl_ID],'+
                 STUFF((SELECT ',['+nodename+']' AS [text()] 
                 FROM cteCompany
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')     
            WHEN 'Contact' THEN
                 '[Company_ID],'+
                 STUFF((SELECT ',['+nodename+']' AS [text()] 
                 FROM cteContact
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')                     
         END) 
         +') VALUES ('+ 
         (CASE nodedesc
            WHEN 'Application' THEN
                 STUFF((SELECT ','''+nodecontents+'''' AS [text()] 
                 FROM cteApplication
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')
            WHEN 'Company' THEN
                 'CAST((SELECT MAX(Appl_ID) FROM #Stg_Application) AS VARCHAR(10))'+','+
                 STUFF((SELECT ','''+nodecontents+'''' AS [text()] 
                 FROM cteCompany
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')     
            WHEN 'Contact' THEN
                 'CAST((SELECT MAX(Company_ID) FROM #Stg_Company) AS VARCHAR(10))'+','+
                 STUFF((SELECT ','''+nodecontents+'''' AS [text()] 
                 FROM cteContact
                 WHERE 
                    nodetype = r.nodetype 
                    AND Appl_ID = r.Appl_ID
                    AND nodename <> 'ID' 
                    AND NULLIF(nodecontents,'') IS NOT NULL
                 ORDER BY ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) 
                 FOR XML PATH('')
                 ),1,1,'')                     
         END)
         +');
         '     
     
    FROM
        (
        SELECT
             Appl_ID
            ,nodetype
            ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder
            ,nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid
            ,nodename
            ,nodecontents
            ,StagingTable
        FROM
            cteApplication a
        UNION ALL
        SELECT
             Appl_ID
            ,nodetype
            ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder
            ,nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid
            ,nodename
            ,nodecontents
            ,StagingTable
        FROM
            cteCompany b
        UNION ALL
        SELECT
             Appl_ID
            ,nodetype
            ,ROW_NUMBER() OVER (PARTITION BY Appl_ID,nodedesc ORDER BY ID) AS typeorder
            ,nodedesc
            ,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY ID) AS nodeid
            ,nodename
            ,nodecontents
            ,StagingTable
        FROM
            cteContact c
        ) r
    WHERE
        typeorder = 1
        AND NULLIF(nodecontents,'') IS NOT NULL
    ORDER BY
        Appl_ID
       ,nodetype       
       ,typeorder
       ,nodeid


    /* Execute the dynamic SQL */

    EXEC sp_executesql @strSQL 



    /* See the results */

    SELECT * FROM #Stg_Application
    SELECT * FROM #Stg_Company
    SELECT * FROM #Stg_Contact
    

END

Post #1437361

« Prev Topic | Next Topic »

Permissions