SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problems with OPENXML - how to avoid using the xmlns


problems with OPENXML - how to avoid using the xmlns

Author
Message
axes2000
axes2000
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 34
hi all,
I am trying to get some data from an xml file into a table in my SQL Server instance; if I use the code below everything works IF AND ONLY IF I delete everything in the <UNODocument tag right at the beginning.
If I leave it there, I get no rows returned.

Is there a way to avoid using the info contained in the mentioned tag?

Thanks,
kowalsky

DECLARE @doc varchar(8000)

SET @doc = '<?xml version="1.0" encoding="UTF-8"?><UNODocument
docID="DispHNRP" xmlns="http://www.theUNO.com/schema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.theUNO.com/schema http://reports.theCASO.com/docrefs/schema/DispHNRP_r1.xsd"
>
<UNODocHeader>
<DocTitle>Commodity Price (HNRP) Report</DocTitle>
<DocRevision>1</DocRevision>
<DocConfidentiality>
<DocConfClass>PUB</DocConfClass>
</DocConfidentiality>
<CreatedAt>2011-04-04T09:01:27</CreatedAt>
</UNODocHeader>
<UNODocBody>
<Date>2011-04-04</Date>
<HNRPs>
<HNRP>
<Hour>1</Hour>
<Price>134.3</Price>
<DataSource>TSO_RP</DataSource>
</HNRP>
<HNRP>
<Hour>2</Hour>
<Price>229.09</Price>
<DataSource>TSO_RP</DataSource>
</HNRP>
<HNRP>
</HNRPs>
</UNODocBody>
</UNODocument>'

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc


SELECT * FROM
OPENXML(@docHandle, '/UNODocument/UNODocBody/HNRPs/HNRP', 2)
WITH
(
[Date] nvarchar(20) '../../Date',
[Hour] nvarchar(20) './Hour',
Price nvarchar(20) './Price')

EXEC sp_xml_removedocument @docHandle
GO
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162917 Visits: 14627
See if something along these lines works for you:


DECLARE @doc NVARCHAR(MAX)

SET @doc = N'
<UNODocument
docID="DispHNRP"
xmlns="http://www.theUNO.com/schema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.theUNO.com/schema http://reports.theCASO.com/docrefs/schema/DispHNRP_r1.xsd">
<UNODocHeader>
<DocTitle>Commodity Price (HNRP) Report</DocTitle>
<DocRevision>1</DocRevision>
<DocConfidentiality>
<DocConfClass>PUB</DocConfClass>
</DocConfidentiality>
<CreatedAt>2011-04-04T09:01:27</CreatedAt>
</UNODocHeader>
<UNODocBody>
<Date>2011-04-04</Date>
<HNRPs>
<HNRP>
<Hour>1</Hour>
<Price>134.3</Price>
<DataSource>TSO_RP</DataSource>
</HNRP>
<HNRP>
<Hour>2</Hour>
<Price>229.09</Price>
<DataSource>TSO_RP</DataSource>
</HNRP>
<HNRP/>
</HNRPs>
</UNODocBody>
</UNODocument>'

DECLARE @docHandle INT
EXEC sp_xml_preparedocument
@docHandle OUTPUT,
@doc,
N'<root xmlns:n="http://www.theUNO.com/schema" />'

--SELECT * FROM OPENXML (@docHandle,'/',3);

SELECT *
FROM OPENXML(@docHandle, '/n:UNODocument/n:UNODocBody/n:HNRPs/n:HNRP', 2)
WITH
(
[Date] NVARCHAR(20) '../../nBigGrinate',
[Hour] NVARCHAR(20) './n:Hour',
Price NVARCHAR(20) './nTonguerice')

EXEC sp_xml_removedocument
@docHandle
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
axes2000
axes2000
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 34
Thank you very much,
it works like a charm,
kowalsky
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162917 Visits: 14627
You're welcome :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
tomashons90mp
tomashons90mp
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1
Thank you very much, it helped me very much. TH
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search