CODEWITHSUNDEEP
sql-serverxml
paul
paul

Reputation: 35

Problems parsing Xml in stored procedure passed from .net to SQL server

I'm having a problem with xml passed from .net to a stored procedure, below is the xml that's passed

DECLARE @XMLDoc AS XML 

SET @XMLDoc = 
            '<ArrayOfProblemType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" 
           xmlns="http://schemas.datacontract.org/2004/07/DatasetServices.JobServices.DataContracts">
        <ProblemType>
           <DateAdded>2011-08-10T11:19:35.187</DateAdded>
                 <EmailTemplate>testing email template</EmailTemplate>
           <ProblemDescription>No Money</ProblemDescription>
           <ProblemTypeId>4</ProblemTypeId>
           <SendEmail>true</SendEmail>
        </ProblemType>
        </ArrayOfProblemType>'

I'm trying to pull data as follows

SELECT 
    ProblemData.Probs.value('(ProblemTypeId)[1]', 'int') as 'ID',   
    ProblemData.Probs.value('(DateAdded)[1]', 'Varchar(50)') as 'DateAdded'  
FROM  
    @XMLDoc.nodes('/ArrayOfProblemType/ProblemType') as ProblemData(Probs)

I'm not getting any errors but I'm not getting any data either.

I've had a look at all the answers prompted to me but none seem to have the xmlns namespaces within the xml. I suspect I need to remove this somehow. Has anyone came across this before that can help?

Thanks Paul

Upvotes: 3

Views: 1100

Answers (2)

marc_s
marc_s

Reputation: 754258

You're not respecting the XML namespaces - you need to do that!

Try this:

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/DatasetServices.JobServices.DataContracts' AS ns)
SELECT 
    ProblemData.Probs.value('(ns:ProblemTypeId)[1]', 'int') as 'ID',   
    ProblemData.Probs.value('(ns:DateAdded)[1]', 'Varchar(50)') as 'DateAdded'  
FROM  
    @XMLDoc.nodes('/ns:ArrayOfProblemType/ns:ProblemType') as ProblemData(Probs)

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You need to use WITH XMLNAMESPACES.

;WITH XMLNAMESPACES (default 'http://schemas.datacontract.org/2004/07/DatasetServices.JobServices.DataContracts')
SELECT ProblemData.Probs.value('(ProblemTypeId)[1]', 'int') as 'ID',   
       ProblemData.Probs.value('(DateAdded)[1]', 'Varchar(50)') as 'DateAdded'  
FROM  @XMLDoc.nodes('/ArrayOfProblemType/ProblemType') as ProblemData(Probs)

Upvotes: 2

Related Questions