March 22, 2013 at 2:12 pm
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
March 22, 2013 at 3:00 pm
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
SubIDSubNameSubAddressContactAddress1ContactAddress2
1Saran123 Usa4534 China7674 India
2Rajesh456 India321 Japan987 Korea
March 22, 2013 at 5:14 pm
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.
March 23, 2013 at 12:59 am
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'
March 30, 2013 at 7:51 pm
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_IDIntIdentity(1,1),
ApplicantEntryCompleteVarchar(50),
AssistantUnderwriterVarchar(50)
)
Create table dbo.Stg_Company
(
Company_IDIntIdentity(1,1),
Appl_IDint,
AddressLine1Varchar(50),
AddressLine2Varchar(50)
)
Create table dbo.Stg_Contact
(
Cont_IDIntIdentity(1,1),
Company_IDint,
PhoneNumberVarchar(50),
Mobilevarchar(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
(
NodeVarchar(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.
April 1, 2013 at 12:14 am
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,
ApplicantEntryCompleteVarchar(50),
AssistantUnderwriterVarchar(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),
BrokerageAddressLine1Varchar(50),
BrokerageAddressLine2Varchar(50),
BrokerageContactName Varchar(50),
BrokeragePhoneNumberVarchar(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),
PhoneNumberVarchar(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
August 31, 2018 at 7:08 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply