Parsing XML with MSSQL 2008



I am having an issue parsing XML with SQL2008. I've tried this 100 different ways and can't get any data out.


I am trying to get data like Title, date_start out of the XML.


The XML is going into my #xml1 table – I can select from @XMLtable or #xml1 and see the XML in the column.


Here is the XML:



<VacancyList xsi:schemaLocation="http://ift.tt/1trvM49">
<Vacancy id="1238059" date_start="2014-08-12">
<Versions>
<Version language="en">
<Title>Web Developer </Title>
<TitleHeading/>
<Categories>
<Item type="area-of-interest" id="32203">SQL</Item>
<Item type="duration" id="permanent">Permanent</Item>
<Item type="extent" id="fulltime">Full-time</Item>
<Item type="operating-time" id="day">Day</Item>
</Categories>
</Version>
</Versions>
</Vacancy>
<Vacancy id="1238059" date_start="2014-07-14">
<Versions>
<Version language="en">
<Title>DBA </Title>
<TitleHeading/>
<Categories>
<Item type="area-of-interest" id="32203">Oracle</Item>
<Item type="duration" id="permanent">Permanent</Item>
<Item type="extent" id="fulltime">Full-time</Item>
<Item type="operating-time" id="day">Day</Item>
</Categories>
</Version>
</Versions>
</Vacancy>
</VacancyList>


Here is my SQL:



USE tempdb
GO

IF OBJECT_ID('tempdb..#xml1') IS NOT NULL DROP TABLE #xml1
CREATE TABLE #xml1 ( yourXML XML )
GO

DECLARE @URL VARCHAR(8000)
SELECT @URL = 'http://ift.tt/15ntxe1'

DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

INSERT #xml1 ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

Declare @XMLOutput xml
SET @XMLOutput = (SELECT yourXML FROM #xml1)

SELECT
val.value('(Title/text())[1]', 'varchar(100)') as Title
FROM
#xml1
CROSS APPLY yourXML.nodes('//Vacancy') AS Txn(svc)
CROSS APPLY svc.nodes('Versions') AS svc(rsp)
CROSS APPLY rsp.nodes('Version') as rsp(val)

No comments:

Post a Comment