asked on

SQL query returning null - error in query

The following query is returning a null for the column description.

Please recommend any other way to get the value.
I think the html is getting in way or my xquery statement needs refining

Here is the entire example:

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

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

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

IF OBJECT_ID('tempdb..##T1') IS NOT NULL
					DROP TABLE ##T1
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
					DROP TABLE ##T2
IF OBJECT_ID('tempdb..##T3') IS NOT NULL
					DROP TABLE ##T3
IF OBJECT_ID('tempdb..#tmpXML') IS NOT NULL
			 DROP TABLE #tmpXML
IF OBJECT_ID('tempdb..#tmpXML2') IS NOT NULL
			 DROP TABLE #tmpXML2


DECLARE @fileName varchar(100)
DECLARE @TableName varchar(Max)
DECLARE @FolderName varchar(100)


DECLARE @ReqID nvarchar(50)
DECLARE @ProjectDescription nvarchar(MAX)

DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML  XML

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
DECLARE @query2 NVARCHAR(MAX);

SET @TableName = 'BringDownCertificate';
SET @FolderName = 'BDCERT'

declare @fileData xml = '
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" 
xmlns:tns="http://tempuri.org/" xmlns:xhtml="http://www.w3.org/1999/xhtml" 
 xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" 
xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" 
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/"
 xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" 
 xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-07-15T21:17:40" 
 xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
	<my:Main>
		<my:CreateDate>2009-02-20</my:CreateDate>
		<my:RequestID>1000195</my:RequestID>
		<my:QuarterEndDate>12/31/2008</my:QuarterEndDate>		
	</my:Main>
	<my:Exceptions_Sec>
		<my:ExceptionsChoice>Exceptions</my:ExceptionsChoice>
		<my:ExceptionDesc_Sec>
			<my:Description>
			<html xmlns="http://www.w3.org/1999/xhtml">
			<div>
			<span style="font-size:12pt;font-family:&amp;apos;Times New Roman&amp;apos;">
			Some fake text here that is not found in my query.
			</span></div></html>
			</my:Description>
		</my:ExceptionDesc_Sec>			
	</my:Exceptions_Sec>
	<my:SubmittingUnit_Sec>
		<my:SubmittingUnit>a place</my:SubmittingUnit>
	</my:SubmittingUnit_Sec>
	<my:Approvals>
		<my:OperationsManager>
			<my:OperationManagerApprovalStatus>Approved</my:OperationManagerApprovalStatus>
			<my:OperationManagerDisplayName>johnny does</my:OperationManagerDisplayName>
			<my:OperationManagerAuthDate>2/20/2009 5:58:18 PM</my:OperationManagerAuthDate>
			<my:OperationManagerComments></my:OperationManagerComments>
			<my:OperationsManagerNTName>NA\person</my:OperationsManagerNTName>
			<my:OperationManagerAssignedTo>a person</my:OperationManagerAssignedTo>
		</my:OperationsManager>
		<my:FinanceManager>
			<my:FinanceManagerApproveStatus>Approved</my:FinanceManagerApproveStatus>
			<my:FinanceManagerDisplayName>the man</my:FinanceManagerDisplayName>
			<my:FinanceManagerAuthDate>2/20/2009 6:04:42 PM</my:FinanceManagerAuthDate>
			<my:FinanceManagerComments></my:FinanceManagerComments>
			<my:FinanceManagerNTName>NA\the man</my:FinanceManagerNTName>
			<my:FinanceManagerAssignedTo>the man</my:FinanceManagerAssignedTo>
		</my:FinanceManager>
		<my:AdditionalSignatory1>
			<my:Signatory1ApproveStatus></my:Signatory1ApproveStatus>
			<my:Signatory1DisplayName></my:Signatory1DisplayName>
			<my:Signatory1Date></my:Signatory1Date>
			<my:Signatory1Comments></my:Signatory1Comments>
			<my:Signatory1NTName></my:Signatory1NTName>
			<my:Signatory1AssignedTo></my:Signatory1AssignedTo>
		</my:AdditionalSignatory1>
		<my:AdditionalSignatory2>
			<my:Signatory2ApproveStatus></my:Signatory2ApproveStatus>
			<my:Signatory2DisplayName></my:Signatory2DisplayName>
			<my:Signatory2Date></my:Signatory2Date>
			<my:Signatory2Comments></my:Signatory2Comments>
			<my:Signatory2NTName></my:Signatory2NTName>
			<my:Signatory2AssignedTo></my:Signatory2AssignedTo>
		</my:AdditionalSignatory2>
	</my:Approvals>
	<my:Variable>
		<my:FormStatus>Completed</my:FormStatus>
		<my:DocAttached>1</my:DocAttached>
		<my:NeedResubmit>0</my:NeedResubmit>
		<my:QuarterYear>2009</my:QuarterYear>
		<my:QuarterMonth>2</my:QuarterMonth>
		<my:SubmittedQuarter>2008 4th Qtr</my:SubmittedQuarter>
	</my:Variable>
	<my:Notification>
		<my:LawDepartmentEmail>me@someone.com</my:LawDepartmentEmail>
		<my:ControllerEmail>me@somseone.com</my:ControllerEmail>
	</my:Notification>
</my:myFields>'



SET @ProjectXML = @fileData

	--Store RequestID as PK
				SELECT @ReqID = xData.Col.value('*:RequestID[1]','nvarchar(50)')
				from @ProjectXML.nodes('/*:myFields/*:Main') AS xData(Col)-- this is the xpath to the individual records we want to extract

			SELECT  n.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
				n.value('text()[1]','NVARCHAR(MAX)') AS Value
				into #T2			
				FROM @ProjectXML.nodes('//*') AS ProjectXML(n)
				Where n.value('local-name(.)','VARCHAR(100)')
				not in
				(    
				    'Description', 
				    'Document',                     
					'root',
					'myFields',
					'Main',
					'Exceptions_Sec',
					'ExceptionDesc_Sec',
					'ExceptionsMain_Sec',
					'SubmittingUnit_Sec',
					'Approvals',
					'OperationsManager',
					'FinanceManager',
					'AdditionalSignatory1',
					'AdditionalSignatory2',
					'Variable',
					'Notification',			
					'div',
					'p',
					'strong',
					'u',
					'span',
					'font',
					'sup',
					'br',
					'table',
					'colgroup',
					'col',
					'tr',
					'td',
					'tbody',
					'b',
					'em',
					'html',
					'h1',
					'h2',
					'h3',
					'h4',
					'h5',
					'h6', 
					'a',
					'i',
					'ul',
					'li')					

			select @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                    from #T2
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
				,1,1,'')

			set @query = 

			---Change from ##t2 to parameter when no html columns
			'SELECT ' + @cols + ' INTO ##T2 from 
				 (
				  select Name, Value
				  from #T2
				) x
				pivot 
				(
					max(Value)
					for Name in (' + @cols + ')
				) p '

			execute(@query);	
		
		     IF OBJECT_ID('tempdb..#tmpXML2') IS NOT NULL
			 DROP TABLE #tmpXML2

		    select @fileData f into #tmpXML2

		   set @Query2 = 'select t1.*, t2.Description
		
			 into ' + @TableName + '
		
				from 

		         (Select * from ##T2)t1


				Left Join

			(Select  n.value(''text()[1]'',''nvarchar(MAX)'') as Description,'
						 + @ReqID + ' as RequestId        						
						FROM #tmpXML2 cross apply f.nodes(''/*:myFields/*:Exceptions_Sec/*:ExceptionDesc_Sec'') AS ProjectXML(n))t2	
				  on t1.RequestID = CAST(t2.RequestId as NVARCHAR(50))'	
			exec (@query2);				
		   
	
		select * from #tmpXML2 
		select * from #T2 
		select * from ##T2
		
	
		 
		 --select * from dbo.BringDownCertificate

Open in new window

I join this at the end which it does at a column but the value is null...instead of the text inside.

      (Select n.value(''text()[1]'',''nvarchar(MAX)'') as Description,'
                                     + @ReqID + ' as RequestId
                                    FROM #tmpXML2 cross apply f.nodes(''/*:myFields/*:Exceptions_Sec/*:ExceptionDesc_Sec'') AS ProjectXML(n))t2
                          on t1.RequestID = CAST(t2.RequestId as NVARCHAR(50))'

Robb Hill

ASKER

I refactored and this is working. My concern with this approach is if I were to have more than one html namespace ..I think this would return more than 1 value..which would not be correct as I only wanted description.

How would you refactor this to just get the one column if there were more than one namespace?

select @fileData f into #tmpXML2

			   ;WITH xmlnamespaces('http://www.w3.org/1999/xhtml' AS ns), cte as
				(
					SELECT @ReqID as RequestID, 1 idx, n.value('local-name(.)','NVARCHAR(MAX)') AS Name, n.value('text()[1]','NVARCHAR(MAX)') AS Value FROM @fileData.nodes('//*[local-name()=''Description'']') AS ProjectXML(n)
					union all
					SELECT @ReqID as RequestID, 2 idx, 'Description' AS Name, n.value('*[1]','NVARCHAR(MAX)') AS Value FROM @fileData.nodes('//ns:html') AS ProjectXML(n)
				)
				select RequestID, Value as Description into ##html from cte
				Where value is not null						

			
		   set @Query2 = 'select t1.*, t2.Description
		
			 into ' + @TableName + '
		
				from 

		         (Select * from ##T2)t1


				Left Join

			(Select  RequestID,Description from ##html)t2
				
				on t1.RequestID = t2.RequestID'
				
			exec (@query2);

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

to get the description, you may try:

Select  n.value(''*[1]'',''nvarchar(MAX)'') as Description

Open in new window

Robb Hill

ASKER

hmm.

So lets say that there was 3 xml elements that all had this same namespace.

Using the withstatement will return them all.

n.value(''*[1]'',''nvarchar(MAX)'') --------------what does this do?

Robb Hill

ASKER

and I would need to keep the empty elements as well.

Robb Hill

ASKER

If we cannot make one namespaces unique for a given xml element in a query result set then this is not a solution.

Please let me know..otherwise we need to move this question forward.

You have already helped in understanding this namespace with statement but if the xml file has this around multiple files this query has multiple rows...which doesnt help for me. Maybe that is the best you can do but if so it doesnt work for what I need.

I would need to dynamically grab all xml fields with said namespace and treat them as separate columns as I do the other values.

I cannot hard code what I do not know.

Ryan Chong

I'm not to sure if I fully understand your requirement, but in order to grab "all values" from a dynamic XML content, we got to set some "patterns" or "rules", so it probably can't be 100% dynamic.

you may raise an attention so that moderator can come in and invite other experts to help out?

Robb Hill

ASKER

I will raise a question. I should have and example soon and I will open another question.

Thank you Mr. Chong:)