Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_xml_preparedocument does not recognize validated xml variable Expand / Collapse
Author
Message
Posted Sunday, May 19, 2013 5:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:26 AM
Points: 36, Visits: 188
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

Post #1454322
Posted Monday, May 20, 2013 1:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,638, Visits: 1,648
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
Post #1454420
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse