Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify node and tag in XML data using T-sql Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
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
Posted Friday, March 22, 2013 3:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721

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
Posted Friday, March 22, 2013 5:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
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
Posted Saturday, March 23, 2013 12:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
Posted Saturday, March 30, 2013 7:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:18 PM
Points: 27, Visits: 304
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
Posted Monday, April 1, 2013 12:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 »

Add to briefcase

Permissions Expand / Collapse