Reading XML Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reading XML Data

Author

Topic

shafiqm
Starting Member

9 Posts

Posted - 2004-03-02 : 08:51:59

Hi,
I want to read this XML data. But the field name and data is grouped together. How I can extract the data from the following using OPENXML
Thanks
</Reports>
<OperatingSystem classname="TOperatingSystem">
<data name="IsNT" type="boolean">1</data>
<data name="OSVersion" type="string">Windows XP</data>
<data name="Platform" type="string">Windows NT</data>
<data name="VersionName" type="string">Uniprocessor Free</data>
<data name="MajorVersion" type="integer">5</data>
<data name="MinorVersion" type="integer">1</data>
<data name="Build" type="integer">2600</data>
<data name="CSD" type="string">Service Pack 1</data>
<data name="ProductID" type="string">55274-640-0542172-23004</data>
<data name="ProductKey" type="string" />
<data name="RegisteredUser" type="string">can3502pc1</data>
<data name="RegisteredOrganisation" type="string">Toys R Us Canada</data>
<data name="DVDRegion" type="string" />
</OperatingSystem>
</Reports>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-02 : 09:28:21

[code]DECLARE @idoc int
DECLARE @doc varchar(8000)
--sample XML document
SET @doc ='
<Reports>
<OperatingSystem classname="TOperatingSystem">
<data name="IsNT" type="boolean">1</data>
<data name="OSVersion" type="string">Windows XP</data>
<data name="Platform" type="string">Windows NT</data>
<data name="VersionName" type="string">Uniprocessor Free</data>
<data name="MajorVersion" type="integer">5</data>
<data name="MinorVersion" type="integer">1</data>
<data name="Build" type="integer">2600</data>
<data name="CSD" type="string">Service Pack 1</data>
<data name="ProductID" type="string">55274-640-0542172-23004</data>
<data name="ProductKey" type="string" />
<data name="RegisteredUser" type="string">can3502pc1</data>
<data name="RegisteredOrganisation" type="string">Toys R Us Canada</data>
<data name="DVDRegion" type="string" />
</OperatingSystem>
</Reports>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/Reports/OperatingSystem/data', 1)
WITH
(
name varchar(20),
type varchar(20),
data ntext 'text()'
)

EXEC sp_xml_removedocument @idoc[/code]
The flags parameter value is set to 1, indicating attribute-centric mapping. As a result, the XML attributes map to the rowset columns defined in SchemaDeclaration.

In SchemaDeclaration (in the WITH clause), the rowset column names, name and type, match the corresponding XML attribute names. Therefore, the ColPattern parameter is not specified. For the data column in the rowset, the XPath function (text()) is specified as ColPattern. This overwrites the attribute-centric mapping specified in flags, and the column contains the leaf-value string of the element content.

shafiqm
Starting Member

9 Posts

Posted - 2004-03-02 : 11:15:47

Thanks ehorn,

I have already done table definition on SQL server and if I have to use Bulk Load using SQLXMLBulkLoad and only requires the last column as data what kind of mapping schema I have to use to extract the last column ( data ) only?

Thanks
Shafiq

shafiqm
Starting Member

9 Posts

Posted - 2004-03-03 : 12:03:27

Any update

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources