SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_xml_preparedocument does not recognize validated xml variable


sp_xml_preparedocument does not recognize validated xml variable

Author
Message
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 216
Can anyone help I am trying to Shred a XML document into a data table in SQL Server.I am using a XML SCHEMA COLLECTION to validate the xml document. Below is a copy of my XML Schema

 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"targetNamespace="http://www.healthcenter.com"
xmlns="http://www.healthcenter.com" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
elementFormDefault="qualified" version="2.0.1.1">

<xs:element name="HealthCenter">
<xs:complexType>
<xs:sequence>
<xs:element name="Patients" type="PatientDetails"/>
<xs:element name="Doctors" type="DoctorsDetails"/>
<xs:element name="Ailment" type="AilementDetails"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="PatientDetails">
<xs:sequence>
<xs:element name="PatientID" type="xs:integer"/>
<xs:element name="FirstName" type="xs:string"/>
<xs:element name="LastName" type="xs:string"/>
<xs:element name="Age" type="xs:string"/>
<xs:element name="AilementID" type="xs:integer"/>
<xs:element name="DoctorID" type="xs:integer"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="DoctorsDetails">
<xs:sequence>
<xs:element name="DoctorID" type="xs:integer"/>
<xs:element name="DoctorName" type="xs:string"/>
<xs:element name="DoctorType" type="DoctorCategory"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name ="AilementDetails">
<xs:sequence>
<xs:element name ="AilementID" type="xs:integer"/>
<xs:element name ="AilementName" type="xs:string"/>
<xs:element name ="AilementCode" type="AilmentCodeTypes"/>
</xs:sequence>
</xs:complexType>


<xs:simpleType name="DoctorCategory">
<xs:restriction base="xs:string">
<xs:enumeration value="Dentist"/>
<xs:enumeration value="PhysioTherapist"/>
<xs:enumeration value="Gynocologist"/>
<xs:enumeration value="GeneralPrctitioner"/>
</xs:restriction>
</xs:simpleType>


<xs:simpleType name="AilmentCodeTypes">
<xs:restriction base="xs:string">
<xs:enumeration value="SDE"/>
<xs:enumeration value="SIE"/>
<xs:enumeration value="SUE"/>
<xs:enumeration value="NRE"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>



--XML Validation
 

DECLARE @XMLDATA XML(dbo.DoctorPatients)
DECLARE @IDOC INT
SET @XMLDATA =
'<HealthCenter xmlns="http://www.healthcenter.com">
<Patients>
<PatientID>1</PatientID>
<FirstName>Micheal</FirstName>
<LastName>Huie</LastName>
<Age>28</Age>
<AilementID>1</AilementID>
<DoctorID>1</DoctorID>
</Patients>
<Doctors>
<DoctorID>1</DoctorID>
<DoctorName>Sheldon Huie</DoctorName>
<DoctorType>Dentist</DoctorType>
</Doctors>
<Ailment>
<AilementID>1</AilementID>
<AilementName>Dementia</AilementName>
<AilementCode>SDE</AilementCode>
</Ailment>
</HealthCenter>'


Here is how I go about shredding the data into a table using sp_xml_preparedocument and OPENXML

EXEC sp_xml_preparedocument @IDOC OUTPUT,@XMLDATA
SELECT *
FROM
OPENXML(@IDOC,'/HealthCenter/Patients',2)
WITH(PatientID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age VARCHAR(3),
AilementID INT,
DoctorID INT
)
EXEC sp_xml_removedocument @IDOC



The above produces this error message in SQL Server

Msg 6628, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1 sp_xml_preparedocument can only process untyped XML. Cast the input value to XML or to a string type.
Msg 8179, Level 16, State 5, Line 26 Could not find prepared statement with handle 0.Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1sp_xml_removedocument: The value
supplied for parameter number 1 is invalid. I think I have done everything right however if someone can
spot why I get such an error message would be greatful. Note I have not yet attempted to insert data into a table, just wanted to display data first.Forgive my formating I had to rearrang in order to get text in code blocks
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3186 Visits: 1779
Hi, The reason why you are getting that message is because sp_xml_preparedocument does not support a typed xml document. Essentially a typed xml document is one that is bound by a schema.

You have a few options from here. One is that could remove the schema declaration against the variable @XMLDATA.. Another is to have a second XML variable that which isn't bound to the xml schema but contains the same xml document and you pass that into sp_xml_preparedocument.

But the approach that I would take is to use the xml method nodes() and value() method to shred the xml. You can keep the schema bound to the xml variable and easily shred the data accordingly.

http://msdn.microsoft.com/en-GB/library/ms188282(v=sql.100).aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search