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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-20 : 09:24:41
|
Graham writes "I have this XML document generated by saving a recordset as xml.
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:AttributeType name="id" rs:number="1" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="data" rs:number="2" rs:nullable="true" rs:maydefer="true" rs:writeunknown="true">
<s:datatype dt:type="string" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row id="1" data="test 1"/>
<z:row id="2" data="test 2"/>
<z:row id="4" data="test 4"/>
<z:row id="5" data="test 5"/>
</rs:data>
</xml>
I am trying to get the data from the XML document using the following SQL script. I am running the script in the SQL Query Analyser on a SQL 2000 Server. Note that I have changed the double quotes in the xml document to single quotes where appropriate.
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @intDoc INT
SET NOCOUNT ON
EXEC sp_xml_preparedocument @intDoc OUTPUT, "<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'><s:Schema id='RowsetSchema'><s:ElementType name='row' content='eltOnly'><s:AttributeType name='id' rs:number='1' rs:maydefer='true' rs:writeunknown='true'><s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/></s:AttributeType><s:AttributeType name='data' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'><s:datatype dt:type='string' dt:maxLength='50'/></s:AttributeType><s:extends type='rs:rowbase'/></s:ElementType></s:Schema><rs:data><z:row id='1' data='test 1'/><z:row id='2' data='test 2'/><z:row id='4' data='test 4'/><z:row id='5' data='test 5'/></rs:data></xml>"
SELECT * FROM OPENXML(@intDoc, '/rs:data/z:row')
WITH (id INT '@id', data VARCHAR(50) '@data')
GO
SET QUOTED_IDENTIFIER ON
GO
I am getting the following error from executing the script
Server: Msg 6603, Level 16, State 1, Line 8
XML parsing error: Reference to undeclared namespace prefix: 'rs'.
The problem lies with the second parameter of the OPENXML function. I have tried changing this to '/data/row' but then I don't get any records returned.
Please can you put me out of my misery and tell me what to do to get at the records.
Regards
Graham" |
|
|
|
|
|
Selecting data from a XML document