October 29, 2007 at 7:14 am
Hi Guys,
I have the XML file (shown below) and i want to extract the category information.. I want to extract both the elements and the attributes...
Can someone help me in this please?
Thanks in advance!
Jonimatix
October 29, 2007 at 7:56 am
Is this what you have in mind:
DECLARE @table1 TABLE (
category nvarchar(50),
startdatedatetime,
enddatedatetime
)
DECLARE @idoc INT
DECLARE @doc VARCHAR(MAX)
SET @doc ='<?xml version="1.0" encoding="utf-8"?>
<n xmlns="http://my.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://my.com">
<vehs xmlns="http://my.com">
<veh sysno="94584" detail_type="HISTORY">
<det>
<category start="08-Jun-1995" end="19-Aug-2003">COMMERCIAL</category>
<category start="19-Aug-2003" end="">GCVOSN 2 0- 3.4</category>
<category start="19-Aug-2003" end="19-Apr-2006">PILOT</category>
</det>
</veh>
</vehs>
</n>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<root xmlns:a="http://my.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'
INSERT INTO @table1 (category, startdate, enddate)
SELECT [category], [startdate], [enddate]
FROM OPENXML(@iDoc, '//a:category', 1)
WITH (
[category] nvarchar(50) '.',
[startdate] nvarchar(50) './@start',
[enddate] nvarchar(50) './@end'
) XmlSet;
EXEC sp_xml_removedocument @iDoc
SELECT * FROM @table1
The result is a table that contains three columns, category is what is in the element, startdate and enddate are the attributes.
Like:
category startdate enddate
-------------------------------------------------- ----------------------- -----------------------
COMMERCIAL 1995-06-08 00:00:00.000 2003-08-19 00:00:00.000
GCVOSN 2 0- 3.4 2003-08-19 00:00:00.000 1900-01-01 00:00:00.000
PILOT
Regards,
Andras
October 29, 2007 at 8:16 am
Amazing!!
Thanks a lot Andras!
October 31, 2007 at 3:06 am
That's really great, I appreciate it.
By the way what's the significance of passing '1' or '2' in
FROM OPENXML(@iDoc, '//a:category', 1)
In both the cases I find same results
October 31, 2007 at 3:13 am
Attribute (1) or element (2) centric mapping. See BOL.
October 31, 2007 at 4:00 am
Andreas, You are a Genius !!!!!!!!!
October 31, 2007 at 4:56 am
Excellent.
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply