December 26, 2015 at 2:02 am
Hi all,
First of all, Merry Christmas 🙂
Next, the problem. I got this following XML:
<VFPData>
<xitems kodeitem="PR040012" namaitem="ROK 688/MWU" pkitem="1CEBA4E7-0DB8-417E-A3AE-002B6ECFA086" />
<xitems kodeitem="PR090141" namaitem="BH SPK 017" pkitem="FD304F86-0F51-4191-82A5-00682D4531C9" />
<xitems kodeitem="PR090072" namaitem="CD 1688" pkitem="0F207101-0F33-4B51-810D-0096F1E81F8D" />
<xitems kodeitem="PR210031" namaitem="BT PYM D WRN" pkitem="8A3A384E-E395-4423-B34C-00A7BA2061F4" />
<xitems kodeitem="PR210019" namaitem="BT BD POLOS" pkitem="0F89A666-16B7-40D6-B04F-0163A81A8321" />
<xitems kodeitem="PR060128" namaitem="LONG CARDIGAN JUMBO" pkitem="CF13CBED-2152-4EA9-90FF-EE4D25B582B9" />
<xitems kodeitem="PR040022" namaitem="ROK POLOS SPDX 3012" pkitem="C54433AE-08CE-4BFA-81B9-EE644D01975A" />
</VFPData>
And I tried to retrieve it using the following T-SQL script:
CREATE TABLE #XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'D:\Project\vfp9\Headx\test.xml'
--– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
Declare @XML XML
select @XML = (select xml_data from #XmlImportTest)
Declare @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML
Select *
FROM OPENXML (@hdoc,'/VFPData/xitems', 0)
with(kodeitem varchar(15) '@KodeItem',
namaitem varchar(40) '@NamaItem',
pkitem uniqueidentifier '@pkItem')
exec sp_xml_removedocument @hdoc
drop table #xmlImportTest
What I got is seven rows of all-null record set.
Can anybody give me a hint where I did wrong? Thanks in advance,
Regards,
foxy
December 26, 2015 at 2:47 am
foxyland.vfp (12/26/2015)
Hi all,First of all, Merry Christmas 🙂
Merry Christmas to you too.
What I got is seven rows of all-null record set.
Can anybody give me a hint where I did wrong? Thanks in advance,
Regards,
foxy
XML is case sensitive which is the reason for the NULL values as the camel case attribute names in the query do not match the actual attribute names.
😎
Note that there is no reason to use the outdated xml document method, simply convert the query to an XQuery
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#XmlImportTest') IS NOT NULL DROP TABLE #XmlImportTest;
DECLARE @TXML XML = '<VFPData>
<xitems kodeitem="PR040012" namaitem="ROK 688/MWU" pkitem="1CEBA4E7-0DB8-417E-A3AE-002B6ECFA086" />
<xitems kodeitem="PR090141" namaitem="BH SPK 017" pkitem="FD304F86-0F51-4191-82A5-00682D4531C9" />
<xitems kodeitem="PR090072" namaitem="CD 1688" pkitem="0F207101-0F33-4B51-810D-0096F1E81F8D" />
<xitems kodeitem="PR210031" namaitem="BT PYM D WRN" pkitem="8A3A384E-E395-4423-B34C-00A7BA2061F4" />
<xitems kodeitem="PR210019" namaitem="BT BD POLOS" pkitem="0F89A666-16B7-40D6-B04F-0163A81A8321" />
<xitems kodeitem="PR060128" namaitem="LONG CARDIGAN JUMBO" pkitem="CF13CBED-2152-4EA9-90FF-EE4D25B582B9" />
<xitems kodeitem="PR040022" namaitem="ROK POLOS SPDX 3012" pkitem="C54433AE-08CE-4BFA-81B9-EE644D01975A" />
</VFPData>';
CREATE TABLE #XmlImportTest
(
xmlFileName VARCHAR(300) NOT NULL
,xml_data XML NOT NULL
);
INSERT INTO #XmlImportTest(xmlFileName,xml_data)
VALUES ('NO FILE',@TXML);
SELECT
XIT.xmlFileName
,XITEMS.DATA.value('@kodeitem','VARCHAR(15)' ) AS kodeitem
,XITEMS.DATA.value('@namaitem','VARCHAR(40)' ) AS namaitem
,XITEMS.DATA.value('@pkitem' ,'UNIQUEIDENTIFIER') AS pkitem
FROM #XmlImportTest XIT
CROSS APPLY XIT.xml_data.nodes('/VFPData/xitems') AS XITEMS(DATA);
Results
xmlFileName kodeitem namaitem pkitem
------------ --------------- --------------------- ------------------------------------
NO FILE PR040012 ROK 688/MWU 1CEBA4E7-0DB8-417E-A3AE-002B6ECFA086
NO FILE PR090141 BH SPK 017 FD304F86-0F51-4191-82A5-00682D4531C9
NO FILE PR090072 CD 1688 0F207101-0F33-4B51-810D-0096F1E81F8D
NO FILE PR210031 BT PYM D WRN 8A3A384E-E395-4423-B34C-00A7BA2061F4
NO FILE PR210019 BT BD POLOS 0F89A666-16B7-40D6-B04F-0163A81A8321
NO FILE PR060128 LONG CARDIGAN JUMBO CF13CBED-2152-4EA9-90FF-EE4D25B582B9
NO FILE PR040022 ROK POLOS SPDX 3012 C54433AE-08CE-4BFA-81B9-EE644D01975A
December 26, 2015 at 3:04 am
Works like a charm. BIG thanks 🙂
January 14, 2016 at 1:52 am
I also had the similar type of problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply