April 28, 2011 at 12:31 pm
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
April 28, 2011 at 1:27 pm
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) '../../n:Date',
[Hour] NVARCHAR(20) './n:Hour',
Price NVARCHAR(20) './n:Price')
EXEC sp_xml_removedocument
@docHandle
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 1:58 pm
Thank you very much,
it works like a charm,
kowalsky
April 28, 2011 at 2:42 pm
You're welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2017 at 3:21 am
Thank you very much, it helped me very much. TH
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply