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?
 
Ryan ChongCommented:
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
 
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 ChongCommented:
can you provide a xml sample file here?
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.

 
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
 
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
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.