xml schema datatype insert - 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 2005 Forums

Transact-SQL (2005)

xml schema datatype insert

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 02:40:20

Hi,
I am using a third party stored procedure to insert data into the table of their database.
The table has nvarchar and xml (with schema) datatypes.

When the following xml parameter is passed to the stored procedure, it works and the data in the xml is indeed inserted into the table.
Here is the xml parameter below

'<ProfileMetaDataFileDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n2="http://schemas.myCompany.com/myProject/[app]/stringList/1.0" xmlns:n3="http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0"
xmlns="http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0"><InvolvedYN>1</InvolvedYN><Client><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>a limited company</value><value>some institution</value></stringList></Client><Office><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>EU</value></stringList></Office>

</ProfileMetaDataFileDetails>'

Note that 
is commented out.
Once again, the above query works fine and a row is inserted into the table with the data inside the above xml.

PROBLEM:

If I uncomment the commented elements in the above xml, then the error is:
XML Validation: Invalid content. Expected element(s):http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0:Associate,http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0:Partner where element 'http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0:AdditionalClientMatter' was specified. Location: /*:ProfileMetaDataFileDetails[1]/*:AdditionalClientMatter[1]

QUESTION:
How do I solve this error please?
Thanks

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-18 : 03:05:22

The error message says that AdditionalClientMatter is not expected/allowed at that position according to the namespace specified in xml. Please look into schema definition for the namespaces and correct them or correct the xml.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 03:10:17

I am now looking at the datatypes. thanks
Will let you know...

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 03:33:24

Hi,
I have now checked the order of the input columns.
I am now adding this extra xml into the original one.
Notice the <FileNumber>

'<ProfileMetaDataFileDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n2="http://schemas.myCompany.com/myProject/[app]/stringList/1.0" xmlns:n3="http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0"
xmlns="http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0"><InvolvedYN>1</InvolvedYN><FileNumber><clientMatter
xmlns="http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0"><value>34993-01326</value></clientMatter></FileNumber><AdditionalClientMatter>12345-76543,76343-998787</AdditionalClientMatter><Client><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>a limited company</value><value>some institution</value></stringList></Client><Office><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>EU</value></stringList></Office>
</ProfileMetaDataFileDetails>'

The error is:
XML Validation: Invalid content. Expected element(s):http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0:client where element 'http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0:value' was specified. Location: /*:ProfileMetaDataFileDetails[1]/*:FileNumber[1]/*:clientMatter[1]/*:value[1]

It seems to me I am doing the right thing but not sure why the error is occuring.
Do y ou see the error please?
Thanks

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-18 : 03:47:32

Without looking at your schema it will be difficult to find the errors. Howevere here it seems that "Value" node is not expected inside clientMatter. Instead it expects "Client" node. Hope you understand how to understand the error

As i mentioned earlier check the schema and xml.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 03:54:14

I have modified the xml to be:

notice <client> and <matter>

Posted - 11/18/2008 : 03:33:24
--------------------------------------------------------------------------------

Hi,
I have now checked the order of the input columns.
I am now adding this extra xml into the original one.
Notice the <FileNumber>

'<ProfileMetaDataFileDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:n2="http://schemas.myCompany.com/myProject/[app]/stringList/1.0" xmlns:n3="http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0"
xmlns="http://schemas.myCompany.com/myProject/[app]/ProfileMetaDataFileDetails/1.0"><InvolvedYN>1</InvolvedYN><FileNumber><clientMatter
xmlns="http://schemas.myCompany.com/myProject/[app]/clientMatter/1.0"><client>34993</client><matter>01326</matter></clientMatter></FileNumber><AdditionalClientMatter>12345-76543,76343-998787</AdditionalClientMatter><Client><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>a limited company</value><value>some institution</value></stringList></Client><Office><stringList
xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>EU</value></stringList></Office>
</ProfileMetaDataFileDetails>'

This seems to be ok now except that the element <additionalClientMatter> does not get inserted and no errors is produced.

Any thoughts please?
p.s. thanks for the previous client tip.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-18 : 04:03:46

How do you say that additionalClientMatter is not getting inserted. Is additionalClientMatter part of schema? Why are you not posting the schema?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 04:07:39

Hi,
I had to edit this post because I found out that the final problem is to do with a missing insert in another complicated long stored procedure.
All seems fine now.
Many thanks for your guidance to solve this issue.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-18 : 04:20:44

I don't see any issue with AdditionalClientMatter element. Can you post what xml did you try save and what actually got saved by querying the table.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-18 : 04:27:41

All solved as the problem was to do with a missing insert.
Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources