Using openrowset and cross apply to import from xml

I am using the code below in a MS sql stored procedure.  This works fine when the delimiters are constant ie. <Unit>"xxxxx"</Unit>.  The problem is the xml file has variable data encapsulated by the delimters ,  so for example <Unit ID="56">"Unit 56"<Unit ID>.  How would I modify this to accommodate the variations in ID=.  Thanks

[DECLARE @TEST varchar(20) = ''


DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\archive\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';

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

INSERT INTO #tempDATA
EXEC ( '
	SELECT 
		  Y.times.query(''Unit ID'').value(''.'', ''varchar(20)'')
		  
	FROM 
	( 
		SELECT CAST(y AS XML)
		FROM OPENROWSET(
		 BULK ' + '''' + @FILENAME + '''' + ' ,
		 SINGLE_BLOB) AS T(y)
	) AS T(y)
	CROSS APPLY y.nodes(''/PCRDATA/Header'') AS Y(Times) ' 
)

SELECT TOP 1 @TEST = TEST FROM #tempDATA


print(@TEST)

Open in new window

LVL 5
WillOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WillOwnerAuthor Commented:
So  I tried this thinking I might be on the right tack, but no to no avail:
DECLARE @TEST varchar(20) = ''


DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\archive\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';

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

INSERT INTO #tempDATA
EXEC ( '
	SELECT 
		  Y.Unit.query(''Unit'').value(''.'', ''varchar(20)'')
		  
	FROM 
	( 
		SELECT CAST(y AS XML)
		FROM OPENROWSET(
		 BULK ' + '''' + @FILENAME + '''' + ' ,
		 SINGLE_BLOB) AS T(y)
	) AS T(y)
	CROSS APPLY y.nodes(''/PCRDATA/Header/Unit/@ID'')[1] AS Y(Unit) ' 
)

SELECT TOP 1 @TEST = TEST FROM #tempDATA


print(@TEST)

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you provide a xml sample file here?
0
WillOwnerAuthor Commented:
Thanks Ryan

<Header>
    <Basesite ID="1729">Life Support x</Basesite>
    <CommandFacility ID="358"> COMMAND</CommandFacility>
    <Region ID="66"> OEMS</Region>
    <Service ID="603">MICU</Service>
    <Timezone ID="2">GMT-5: Eastern Standard (EST)</Timezone>
    <Unit ID="4240">Unit 3</Unit>
    <DateToPrint>dt_disp</DateToPrint>
    <PRID>#######</PRID>
    <ParentPRID>#########</ParentPRID>
    <ServiceName> EMS-MICU</ServiceName>
    <ServicePhone1>xxxxxxxxx</ServicePhone1>
  </Header>

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

WillOwnerAuthor Commented:
This what I came up with so far:
IF OBJECT_ID('tempdb..#tempDATA4') IS NOT NULL DROP TABLE #tempDATA4
CREATE TABLE #tempDATA4 ( Unit VARCHAR(20) , Nature VARCHAR(20) , Disposition VARCHAR(20), ResponseMode VARCHAR(20), TransportMode VARCHAR(20), Crew1 VARCHAR(20),Crew2 VARCHAR(20), Crew3 VARCHAR(20))

declare @xml xml 
set @xml=(
  SELECT *
  FROM OPENROWSET(BULK + '''' + @FILENAME + '''' +, SINGLE_BLOB) AS data
)

select @xml.value('(/PCRDATA/Header/Unit/@ID)[1]', 'nvarchar(max)') as unit,
@xml.value('(/PCRDATA/General/DispatchedAs/@ID)[1]', 'nvarchar(max)') as Nature,
@xml.value('(/PCRDATA/General/Outcome/@ID)[1]', 'nvarchar(max)')as Disposition,
@xml.value('(/PCRDATA/Referring/ModetoRef/@ID)[1]', 'nvarchar(max)')as ResponseMode,
@xml.value('(/PCRDATA/Receiving/ModetoRec/@ID)[1]', 'nvarchar(max)')as TransportMode,
@xml.value('(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[1]', 'nvarchar(max)') as Crew1,
@xml.value('(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[2]', 'nvarchar(max)') as Crew2,
@xml.value('(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[3]', 'nvarchar(max)') as Crew3
INSERT INTO #tempDATA4 (Unit,Nature,Disposition,ResponseMode,TransportMode,Crew1,Crew2,Crew3) values((Unit,Nature,Disposition,ResponseMode,TransportMode,Crew1,Crew2,Crew3)

SELECT TOP 1 @Unit=Unit,
@Nature=Nature,@Disposition=Disposition,@ResponseMode=ResponseMode,@TransportMode=TransportMode,@Crew1=Crew1,@Crew2=Crew2,@Crew3=Crew3


 FROM #tempDATA4

Open in new window


Not sure of the syntax regarding the variable filename.  I also what to read the data in the Unit node beside the ID.  I did get the query to report back the ID value.
0
WillOwnerAuthor Commented:
I was able to get this compiled but returns error:

Msg 8114, Level 16, State 5, Procedure MICU_XML5_TEST, Line 122
Error converting data type varchar to numeric.


IF OBJECT_ID('tempdb..#tempDATA4') IS NOT NULL DROP TABLE #tempDATA4
CREATE TABLE #tempDATA4 ( Unit VARCHAR(20) , Nature VARCHAR(20) , Disposition VARCHAR(20), ResponseMode VARCHAR(20), TransportMode VARCHAR(20), Crew1 VARCHAR(20),Crew2 VARCHAR(20), Crew3 VARCHAR(20))

insert into #tempDATA4 
exec('


declare @xml xml 
set @xml=(
  SELECT *
  FROM OPENROWSET(BULK' + '''' + @FILENAME + '''' +', SINGLE_BLOB) AS data
)

select @xml.value(''(/PCRDATA/Header/Unit/@ID)[1]'', ''varchar(max)'') as unit,
@xml.value(''(/PCRDATA/General/DispatchedAs/@ID)[1]'', ''varchar(max)'') as Nature,
@xml.value(''(/PCRDATA/General/Outcome/@ID)[1]'', ''varchar(max)'')as Disposition,
@xml.value(''(/PCRDATA/Referring/ModetoRef/@ID)[1]'', ''varchar(max)'')as ResponseMode,
@xml.value(''(/PCRDATA/Receiving/ModetoRec/@ID)[1]'', ''varchar(max)'')as TransportMode,
@xml.value(''(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[1]'', ''varchar(max)'') as Crew1,
@xml.value(''(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[2]'', ''varchar(max)'') as Crew2,
@xml.value(''(/PCRDATA/Crew/CrewMembers/CrewMember/CrewName/@ID)[3]'', ''varchar(max)'') as Crew3')


SELECT TOP 1 @Unit=Unit,
@Nature=Nature,@Disposition=Disposition,@ResponseMode=ResponseMode,@TransportMode=TransportMode,@Crew1=Crew1,@Crew2=Crew2,@Crew3=Crew3


 FROM #tempDATA4

Open in new window

0
WillOwnerAuthor Commented:
Looks like it was bad data without an error test.  Still not producing expected output from query above
0
WillOwnerAuthor Commented:
Got it to work.  Is there a way now to use the Value ID to capture data in the node?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
not too sure why you need to use CROSS APPLY in your case which I think it's not necessary, unless there's a reason behind.

This works fine when the delimiters are constant ie. <Unit>"xxxxx"</Unit>.  The problem is the xml file has variable data encapsulated by the delimters ,  so for example <Unit ID="56">"Unit 56"<Unit ID>.

you could try:

declare @PRID_S varchar(10)
set @PRID_S = 'test'

DECLARE @TEST varchar(20) = ''

DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\archive\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';

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

set @sql = '
	SELECT 
	T.y.query(''/PCRDATA/Header/Unit'').value(''.'', ''varchar(20)'') Unit_Value
	FROM 
	( 
		SELECT CAST(y AS XML)
		FROM OPENROWSET(
		 BULK '''+@FILENAME+''' ,
		 SINGLE_BLOB) AS T(y)
	) AS T(y) '

print (@sql)

INSERT INTO #tempDATA
EXEC (@sql)

SELECT TOP 1 @TEST = TEST FROM #tempDATA

print(@TEST)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.