Trying to extract name from xml file

Here is the xml file

<Crew>
    <CrewMembers>
      <CrewMember>
        <CrewName ID="35124">JOHN DOE</CrewName>
        <CrewRole ID="7">Not Applicable</CrewRole>
        <CrewLevel>EMT-Paramedic</CrewLevel>
        <CrewSequence>1</CrewSequence>
      </CrewMember>
    
    </CrewMembers>

Open in new window


Here is the query

DECLARE @PRID_S varchar(10) set @PRID_S='48967264'

DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\archive\'+@PRID_S+'.XML'
Declare @CrewName1 varchar(30)
Declare @Crew1 varchar(10) set @crew1='35124'

IF OBJECT_ID('tempdb..#tempDATA4') IS NOT NULL DROP TABLE #tempDATA4
CREATE TABLE #tempDATA4 (  CrewName1 VARCHAR(20))

INSERT INTO #tempDATA4
exec(
'declare @xml xml 
set @xml=(
  SELECT *
  FROM OPENROWSET(BULK'+ ''''+@FILENAME+''''+', SINGLE_BLOB) AS data
)

select 

@xml.value(''(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/ID='+ ''''+@Crew1+''''+', ''nvarchar(max)'') as CrewName')

SELECT TOP 1 @CrewName1=CrewName1 FROM #tempDATA4
print(@CrewName1)

Open in new window


The error I am getting is Unclosed quotation mark after the character string ') as CrewName'.  

what am I doing wrong

Thanks
LVL 5
WillOwnerAsked:
Who is Participating?
 
zc2Commented:
Please try this:
@xml.query(''/PCRDATA/Crew/CrewMembers/CrewMember/CrewName[@ID="'+@Crew1+'"]'').value(''.'', ''nvarchar(max)'') as CrewName' )

Open in new window

Also, in the the XML you posted, the root element is "Crew". But your XPath expression starts with "PCRDATA". Is "Crew" actually a child of "PCRDATA" ?
1
 
kenfcampCommented:
My guess is that

@xml.value(''(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/ID='+ ''''+@Crew1+''''+', ''nvarchar(max)'') as CrewName')

is the issue

Try changing CrewName' to CrewName
0
 
WillOwnerAuthor Commented:
Thanks for your reply. Didn't work.   It created errors after CrewName
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
zc2Commented:
I have reformatted a little the string you pass to the exec() :
exec( 'declare @xml xml 
set @xml=(
  SELECT *
  FROM OPENROWSET(BULK'+ ''''+@FILENAME+''''+', SINGLE_BLOB) AS data
)
select 
@xml.value(''/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/ID="'+@Crew1+'"'', ''nvarchar(max)'') as CrewName' )

Open in new window

0
 
WillOwnerAuthor Commented:
Thank you. That executed but returned a "false" on Print(@Crewname1)
0
 
WillOwnerAuthor Commented:
Thank you. Works Great
0
 
zc2Commented:
You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.