Identify node and tag in XML data using T-sql

  • unnati.patel513

    Say Hey Kid

    Points: 697

    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

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    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

  • unnati.patel513

    Say Hey Kid

    Points: 697

    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.

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    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'

  • unnati.patel513

    Say Hey Kid

    Points: 697

    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.

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    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

  • subrat.shasank

    Newbie

    Points: 9

    I got this as error when Try and execute the  procedure with my XML

    Msg 6603, Level 16, State 2, Procedure ParseXML, Line 207
    XML parsing error: Expected token 'eof' found '>'.

    /ax-app-->><--/descendant-or-self::node()|/ax-app>//@*|/ax-app>//@*/child::text()

    ;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
      )SELECT * FROM TreeList
        ,
      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

    I think this part is throwing the error

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply