reduce databse size: xml

Showing posts with label xml. Show all posts

Wednesday, March 21, 2012

Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!
Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>
|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert[vbcol=seagreen]
element
> as
>

Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert
element
> as
>

Referencing xs:schema within a SCHEMA COLLECTION

end and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:

> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in you
r
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the w

end
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
>|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:

> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:
>

Referencing xs:schema within a SCHEMA COLLECTION

Hi all,
I have an XML schema that has an element declaration that references a W3C
XML Schema type:
<xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
. . .
<xs:element ref="xs:schema"/>
When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
error:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
The problem is obviously that the schema processor does not have a copy of
the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
Schema reference from the W3C and cleaning up the documentation nodes
(riddled with single quotes). The schema processor then gave me an "invalid
target namespace specified error.
Any ideas for having a node that contains a schema? How would the schema
for WSDL be accepted? Thanks,
- Erik
Hi Eric,
You don't need to import the schema for schemas (apparently it even causes
problems) because its built-in to SQL Server 2005. Just refer to it in your
schema. Like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
...
<xs:element name="yourtype" type="xs:string" />
</xs:schema>
Deriving types would work the same way.
Weird error message, I'll admit.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
> Hi all,
> I have an XML schema that has an element declaration that references a W3C
> XML Schema type:
> <xs:import namespace="http://www.w3.org/2001/XMLSchema"/>
> . . .
> <xs:element ref="xs:schema"/>
> When I try and import this schema into a SQL SCHEMA COLLECTION, I get an
> error:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> The problem is obviously that the schema processor does not have a copy of
> the W3C XML Schema, uh, schema handy. I tried getting a copy of the XML
> Schema reference from the W3C and cleaning up the documentation nodes
> (riddled with single quotes). The schema processor then gave me an
> "invalid
> target namespace specified error.
> Any ideas for having a node that contains a schema? How would the schema
> for WSDL be accepted? Thanks,
> - Erik
>
|||Thanks Bob.
If I declare the schema type as xs:string, I'll have to escape XML markup in
the embedded schema node. The work-around I picked instead was to declare an
xs:any rather than an xs:element. I can tell the schema process to skip
processing on the embedded node that way.
But I really wanted to have SQL Server validate my documents fully,
including the embedded schemas. I'll think about that over the weekend and
let everyone know what I come up with.
Cheers,
Erik Johnson
http://appside.blogspot.com
"Bob Beauchemin" wrote:

> Hi Eric,
> You don't need to import the schema for schemas (apparently it even causes
> problems) because its built-in to SQL Server 2005. Just refer to it in your
> schema. Like this:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" ...more >
> ...
> <xs:element name="yourtype" type="xs:string" />
> </xs:schema>
> Deriving types would work the same way.
> Weird error message, I'll admit.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:E4DBFFC7-28FF-4C89-BD0A-F31502BA3E36@.microsoft.com...
>
>
|||Hi Erik, could you please send me email to describe what you would like to
do? We then can take a look at why it is not working.
Note that the Schema for the W3C Schema is not a valid schema in its own.
Best regards
Michael
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...[vbcol=seagreen]
> Thanks Bob.
> If I declare the schema type as xs:string, I'll have to escape XML markup
> in
> the embedded schema node. The work-around I picked instead was to declare
> an
> xs:any rather than an xs:element. I can tell the schema process to skip
> processing on the embedded node that way.
> But I really wanted to have SQL Server validate my documents fully,
> including the embedded schemas. I'll think about that over the weekend
> and
> let everyone know what I come up with.
> Cheers,
> Erik Johnson
> http://appside.blogspot.com
> "Bob Beauchemin" wrote:
|||Hi Michael,
Thanks for looking into this. We are creating some schemas for XML
documents that contain XML schemas. Here is a simple example:
CREATE XML SCHEMA COLLECTION TestSchemas AS
'<xs:schema id="Noun"
targetNamespace="http://tempuri.org/Noun.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/Noun.xsd"
xmlns:mstns="http://tempuri.org/Noun.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://www.w3.org/2001/XMLSchema" />
<xs:element name="Noun">
<xs:complexType>
<xs:sequence>
<xs:element ref="xs:schema" />
</xs:sequence>
<xs:attribute name="uri" use="required">
<xs:simpleType>
<xs:restriction base="xs:anyURI"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
I get this message:
Msg 2308, Level 16, State 1, Line 1
Reference to an undefined name 'schema' within namespace
'http://www.w3.org/2001/XMLSchema'
And you are right. The sample XSD on the W3C website is not usable,
although I tried to shoehorn it! Thanks again,
- Erik
"Michael Rys [MSFT]" wrote:

> Hi Erik, could you please send me email to describe what you would like to
> do? We then can take a look at why it is not working.
> Note that the Schema for the W3C Schema is not a valid schema in its own.
> Best regards
> Michael
> "Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
> news:228DE552-E347-4906-A1E0-F321E00FF3BC@.microsoft.com...
>
>
|||I see. Even if you remove the import, this fails. Also means that I can't
catalog the schemas in the WSDL file put out by SQL Server 2005 XML Web
Services feature. For example,
http://schemas.microsoft.com/sqlserver/2004/SOAP/types. Even if I change the
target namespace. Hmmm...
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Erik J." <ErikJ@.discussions.microsoft.com> wrote in message
news:EDC61844-1E9B-4B71-8B15-4686FAAB8A5F@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for looking into this. We are creating some schemas for XML
> documents that contain XML schemas. Here is a simple example:
> CREATE XML SCHEMA COLLECTION TestSchemas AS
> '<xs:schema id="Noun"
> targetNamespace="http://tempuri.org/Noun.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/Noun.xsd"
> xmlns:mstns="http://tempuri.org/Noun.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:import namespace="http://www.w3.org/2001/XMLSchema" />
> <xs:element name="Noun">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="xs:schema" />
> </xs:sequence>
> <xs:attribute name="uri" use="required">
> <xs:simpleType>
> <xs:restriction base="xs:anyURI"/>
> </xs:simpleType>
> </xs:attribute>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> I get this message:
> Msg 2308, Level 16, State 1, Line 1
> Reference to an undefined name 'schema' within namespace
> 'http://www.w3.org/2001/XMLSchema'
> And you are right. The sample XSD on the W3C website is not usable,
> although I tried to shoehorn it! Thanks again,
> - Erik
>
> "Michael Rys [MSFT]" wrote:

Monday, March 12, 2012

Reference to an undefined name !

Hello all,
I am trying to add a XML schema but I get this error for the following XSD.
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'fullpersoninfo'
What am I doing wrong?. Please help!
******************
create XML schema collection test_schema_coll
as
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">
<xs:element name="employee" type="fullpersoninfo">
<xs:complexType name="personinfo">
<xs:sequence>
<xs:element name="firstname" type="xs:string"/>
<xs:element name="lastname" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="fullpersoninfo">
<xs:complexContent>
<xs:extension base="personinfo">
<xs:sequence>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:element>
</xs:schema>'
******************
Thanks,
GaneshHi Ganesh,
Couple of things:
1. You need to define a namespace prefix for the items in your new schema in
order to refer to them. Else, you're referring to a type in no namespace.
2. Your global element declaration is surrounding the definition of your
complexTypes.
Hope this helps
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
Here's one that works:
create XML schema collection test_schema_coll
as
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://MyXMLDocSchema"
elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">

<xs:complexType name="personinfo">
<xs:sequence>
<xs:element name="firstname" type="xs:string"/>
<xs:element name="lastname" type="xs:string"/>
</xs:sequence>
</xs:complexType>


<xs:complexType name="fullpersoninfo">
<xs:complexContent>
<xs:extension base="tns:personinfo">
<xs:sequence>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>



<xs:element name="employee" type="tns:fullpersoninfo">
</xs:element>
</xs:schema>'
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:BBB836E3-1EEE-4333-A884-360340A52B1A@.microsoft.com...
> Hello all,
> I am trying to add a XML schema but I get this error for the following
> XSD.
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'fullpersoninfo'
> What am I doing wrong?. Please help!
> ******************
> create XML schema collection test_schema_coll
> as
> N'<?xml version="1.0"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">
> <xs:element name="employee" type="fullpersoninfo">
> <xs:complexType name="personinfo">
> <xs:sequence>
> <xs:element name="firstname" type="xs:string"/>
> <xs:element name="lastname" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="fullpersoninfo">
> <xs:complexContent>
> <xs:extension base="personinfo">
> <xs:sequence>
> <xs:element name="address" type="xs:string"/>
> <xs:element name="city" type="xs:string"/>
> <xs:element name="country" type="xs:string"/>
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> ******************
> Thanks,
> Ganesh

Reference to an undefined name !

Hello all,
I am trying to add a XML schema but I get this error for the following XSD.
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'fullpersoninfo'
What am I doing wrong?. Please help!
******************
create XML schema collection test_schema_coll
as
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">
<xs:element name="employee" type="fullpersoninfo">
<xs:complexType name="personinfo">
<xs:sequence>
<xs:element name="firstname" type="xs:string"/>
<xs:element name="lastname" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="fullpersoninfo">
<xs:complexContent>
<xs:extension base="personinfo">
<xs:sequence>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:element>
</xs:schema>'
******************
Thanks,
Ganesh
Hi Ganesh,
Couple of things:
1. You need to define a namespace prefix for the items in your new schema in
order to refer to them. Else, you're referring to a type in no namespace.
2. Your global element declaration is surrounding the definition of your
complexTypes.
Hope this helps
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
Here's one that works:
create XML schema collection test_schema_coll
as
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:tns="http://MyXMLDocSchema"
elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">

<xs:complexType name="personinfo">
<xs:sequence>
<xs:element name="firstname" type="xs:string"/>
<xs:element name="lastname" type="xs:string"/>
</xs:sequence>
</xs:complexType>


<xs:complexType name="fullpersoninfo">
<xs:complexContent>
<xs:extension base="tns:personinfo">
<xs:sequence>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>



<xs:element name="employee" type="tns:fullpersoninfo">
</xs:element>
</xs:schema>'
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:BBB836E3-1EEE-4333-A884-360340A52B1A@.microsoft.com...
> Hello all,
> I am trying to add a XML schema but I get this error for the following
> XSD.
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'fullpersoninfo'
> What am I doing wrong?. Please help!
> ******************
> create XML schema collection test_schema_coll
> as
> N'<?xml version="1.0"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">
> <xs:element name="employee" type="fullpersoninfo">
> <xs:complexType name="personinfo">
> <xs:sequence>
> <xs:element name="firstname" type="xs:string"/>
> <xs:element name="lastname" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="fullpersoninfo">
> <xs:complexContent>
> <xs:extension base="personinfo">
> <xs:sequence>
> <xs:element name="address" type="xs:string"/>
> <xs:element name="city" type="xs:string"/>
> <xs:element name="country" type="xs:string"/>
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> ******************
> Thanks,
> Ganesh

Friday, March 9, 2012

Reference ID problem for import to SQL

I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
all data are insert into tables, but I want a reference element (foreign key
to a Id in a higher level element), that inherits a ID from another element.
I have a regular XML file and a mapping XSD file.
XML file:
<DKMaster>
<continent>
<name>Europa</name>
<country>
<name>Denmark</name>
<location>
<name>Kolding</name>
</location>
............ ' recursive
</country>
............ 'recursive
</continent>
..............' recursive
</DKMaster>
XSD file:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Cont_Loc" parent="continent" parent-key="Id"
child="location" child-key="ContinentId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
<xsd:element name="ContinentId" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I want to reference the Continent/Id to Location/ContinentID, how is this
possible ?
Database structure:
Table Continent
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
Table Location
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
- ContinentId int F.K.
"Magni" wrote:

> I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
> all data are insert into tables, but I want a reference element (foreign key
> to a Id in a higher level element), that inherits a ID from another element.
> I have a regular XML file and a mapping XSD file.
> XML file:
> <DKMaster>
> <continent>
> <name>Europa</name>
> <country>
> <name>Denmark</name>
> <location>
> <name>Kolding</name>
> </location>
> ............ ' recursive
> </country>
> ............ 'recursive
> </continent>
> ..............' recursive
> </DKMaster>
> XSD file:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="Cont_Loc" parent="continent" parent-key="Id"
> child="location" child-key="ContinentId" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> <xsd:element name="ContinentId" type="xsd:integer" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I want to reference the Continent/Id to Location/ContinentID, how is this
> possible ?
|||Hi Magni,
The location element should be defined as a child of the continent element
in the schema.. (see the example below)
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="location" ref="location" sql:relation="Continent"
sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
However I see one more problem though..
Your data has
<continent>
<country>
<location>
In your schema you have not defined the <country> element though.
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Magni" <Magni@.discussions.microsoft.com> wrote in message
news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...[vbcol=seagreen]
> Database structure:
> Table Continent
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> Table Location
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> - ContinentId int F.K.
> "Magni" wrote:
working,[vbcol=seagreen]
key[vbcol=seagreen]
element.[vbcol=seagreen]
recursive[vbcol=seagreen]
sql:relation="Continent"[vbcol=seagreen]
this[vbcol=seagreen]
|||"Chandra Kalyanaraman [MSFT]" wrote:

> Hi Magni,
> The location element should be defined as a child of the continent element
> in the schema.. (see the example below)
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="location" ref="location" sql:relation="Continent"
> sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> However I see one more problem though..
> Your data has
> <continent>
> <country>
> <location>
> In your schema you have not defined the <country> element though.
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Magni" <Magni@.discussions.microsoft.com> wrote in message
> news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...
> working,
> key
> element.
> recursive
> sql:relation="Continent"
> this
>
>
Hi Chandra
I am ahaving a Same kind of Issue
<Student>
<School>
<ApCourse>
</ApCourse>
</School>
</Student>
I am trying to Insert into ApCourse, the value from School --> SchoolID
I tried with your IDea of Using a reference ,but its throwing me a Error
Hi,
I have to Insert into 3 tables data coming as a XML file
DistrictStudent PK--> Auto ID
SchoolStudent PK -->DistrictStudent_AutoId
Tablle is referenced by Foreign key
SchoolStudentAPCourse: FK_SchoolStudentAPCourse_SchoolStudent
SchoolStudentAPCourse PK -->SchoolStudentId
I am able to Insert Data into two tables, but when I want to Insert into the
third table( SchoolStudentAPCourse ), its giving me Error
Here is the Schema formy File
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:mstns="http://tempuri.org/XMLSchema1.xsd">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="DSSS"parent="DistrictStudent"
parent-key="AutoId"
child="SchoolStudent"
child-key="DistrictStudent_AutoId" />
<sql:relationship name="SchoolStudentAPCourse" parent="SchoolStudent"
parent-key="SchoolStudentId"
child="SchoolStudentApCourse"
child-key="SchoolStudentId"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:elementname="Student" sql:relation="DistrictStudent"
sql:key-fields="DataVersionId">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DataVersionId"
type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element name="SSID"type="xsd:int"sql:field="SSID" />
<xsd:element name="SSN"type="xsd:string"sql:field="SSN" />
<xsd:element
name="FirstName"type="xsd:string"sql:field="FirstName"/>
<xsd:element
name="MiddleName"type="xsd:string"sql:field="MiddleName" />
<xsd:element
name="LastName"type="xsd:string"sql:field="LastName"/>
<xsd:element
name="BirthDate"type="xsd:date"sql:field="BirthDate" />
<xsd:element
name="GenderTypeCode"type="xsd:string"sql:field="GenderTypeCode"/>
<xsd:element
name="RaceTypeId"type="xsd:int"sql:field="RaceTypeId" />
<xsd:element
name="DisabililtyTypeId"type="xsd:int"sql:field="DisabilityTypeId"/>
<xsd:element
name="PrimaryLanguageId"type="xsd:int"sql:field="PrimaryLanguageId"/>
<xsd:element
name="LanguageSpokenAtHomeId"type="xsd:int"sql:field="LanguageSpokenAtHomeId"/>
<xsd:element
name="ZipCode"type="xsd:string"sql:field="ZipCode"/>
<xsd:element
name="ZipCodeExt"type="xsd:string"sql:field="ZipCodeExt"/>
<xsd:element
name="ExpectedGradYear"type="xsd:string"sql:field="ExpectedGradYear"/>
<xsd:element name="ResidentSchoolDistrictOrganizationId"
type="xsd:int"sql:field="ResidentSchoolDistrictOrganizationId"/>
<xsd:element
name="PreSchool"type="xsd:int"sql:field="PreSchool"/>
<xsd:element
name="IsHomeless"type="xsd:int"sql:field="IsHomeless"/>
<xsd:element
name="IsForeignExchange"type="xsd:int"sql:field="IsForeignExchange"/>
<xsd:element
name="IsHomeBasedStudentAttendingPartTime"type="xsd:int"sql:field="IsHomeBasedStudentAttendingPartTime"/>
<xsd:element
name="IsApprovedPrivateSchoolStudentAttendingPartT ime"type="xsd:int"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="DateEnrolledInDistrict"type="xsd:date"sql:field="DateEnrolledInDistrict"/>
<xsd:element
name="DateExitedDistrict"type="xsd:date"sql:field="DateExitedDistrict"/>
<xsd:element
name="ELLEnrollmentDate"type="xsd:date"sql:field="ELLEnrollmentDate"/>
<xsd:element
name="ELLExitDate"type="xsd:date"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="GradeLevelId"type="xsd:int"sql:field="GradeLevelId" />
<xsd:element name="GPA"type="xsd:decimal"sql:field="GPA"
/>
<xsd:element
name="FreeReducedMealStatus"type="xsd:int"sql:field="FreeReducedMealStatus" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy"/>
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated"/>
<xsd:element
name="MatchType"type="xsd:string"sql:field="MatchType"/>
<xsd:element name="School" sql:relation="SchoolStudent"
sql:key-fields="DistrictStudent_AutoId"sql:relationship="DSSS">
<xsd:complexType>
<xsd:sequence>

<xsd:element
name="DataVersionId"type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="SchoolOrganizationId"type="xsd:int"sql:field="SchoolOrganizationId" />
<xsd:element
name="IsPrimarySchool"type="xsd:int"sql:field="IsPrimarySchool" />
<xsd:element
name="DateEnrolledInSchool"type="xsd:date"sql:field="DateEnrolledInSchool" />
<xsd:element
name="DateExitedSchool"type="xsd:date"sql:field="DateExitedSchool" />
<xsd:element
name="EnrollmentStatusTypeId"type="xsd:int"sql:field="EnrollmentStatusTypeId" />
<xsd:element
name="NumDaysAttended"type="xsd:decimal"sql:field="NumDaysAttended" />
<xsd:element
name="NumDaysEnrolled"type="xsd:decimal"sql:field="NumDaysEnrolled" />
<xsd:element
name="NumUnexcusedAbsences"type="xsd:int"sql:field="NumUnexcusedAbsences" />
<xsd:element
name="IsSchoolChoice"type="xsd:int"sql:field="IsSchoolChoice" />
<xsd:element
name="IsLAPReading"type="xsd:int"sql:field="IsLAPReading" />
<xsd:element
name="IsLAPMath"type="xsd:int"sql:field="IsLAPMath" />
<xsd:element
name="IsTASReading"type="xsd:int"sql:field="IsTASReading" />
<xsd:element
name="IsTASMath"type="xsd:int"sql:field="IsTASMath" />
<xsd:element
name="IsTitleIMigrant"type="xsd:int"sql:field="IsTitleIMigrant" />
<xsd:element
name="IsSection504"type="xsd:int"sql:field="IsSection504" />
<xsd:element
name="Is21stCentury"type="xsd:int"sql:field="Is21stCentury" />
<xsd:element
name="IsSupplementalServices"type="xsd:int"sql:field="IsSupplementalServices" />
<xsd:element name="IsGifted"type="xsd:int"sql:field="IsGifted" />
<xsd:element
name="IsBilingualProgram"type="xsd:int"sql:field="IsBilingualProgram" />
<xsd:element
name="IsSpecialEd"type="xsd:int"sql:field="IsSpecialEd" />
<xsd:element
name="SpecialEdLREPartATypeId"type="xsd:int"sql:field="SpecialEdLREPartATypeId" />
<xsd:element
name="SpecialEdLREPartBTypeId"type="xsd:int"sql:field="SpecialEdLREPartBTypeId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
<xsd:element name="APCourses" sql:relation="SchoolStudentAPCourse"
sql:relationship="SchoolStudentAPCourse">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="APCourse"type="xsd:int"sql:field="APCourseId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here is the Input File XML
<?xml version="1.0" encoding="utf-8" ?>
<SSID>
<Student>
<DataVersionId>5</DataVersionId>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SSID>123456789</SSID>
<SSN>123456789</SSN>
<FirstName>Cat</FirstName>
<MiddleName>Peka</MiddleName>
<LastName>Boo</LastName>
<BirthDate>12/31/1986</BirthDate>
<GenderTypeCode>f</GenderTypeCode>
<RaceTypeId>1</RaceTypeId>
<DisabililtyTypeId>1</DisabililtyTypeId>
<PrimaryLanguageId>2</PrimaryLanguageId>
<LanguageSpokenAtHomeId>1</LanguageSpokenAtHomeId>
<ZipCode>98123</ZipCode>
<ZipCodeExt>1234</ZipCodeExt>
<ExpectedGradYear>2006</ExpectedGradYear>
<ResidentSchoolDistrictOrganizationId>29103</ResidentSchoolDistrictOrganizationId>
<PreSchool>1</PreSchool>
<IsHomeless>1</IsHomeless>
<IsForeignExchange>1</IsForeignExchange>
<IsHomeBasedStudentAttendingPartTime>1</IsHomeBasedStudentAttendingPartTime>
<IsApprovedPrivateSchoolStudentAttendingPartTime>1 </IsApprovedPrivateSchoolStudentAttendingPartTime>
<DateEnrolledInDistrict>01/01/2002</DateEnrolledInDistrict>
<DateExitedDistrict />
<ELLEnrollmentDate />
<ELLExitDate />
<GradeLevelId>15</GradeLevelId>
<GPA>3.4</GPA>
<FreeReducedMealStatus>1</FreeReducedMealStatus>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/27/2004</LastUpdated>
<MatchType>S</MatchType>
<School>
<DataVersionId>5</DataVersionId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<SchoolOrganizationId>14</SchoolOrganizationId>
<IsPrimarySchool>1</IsPrimarySchool>
<DateEnrolledInSchool>07/22/2002</DateEnrolledInSchool>
<DateExitedSchool>09/25/2002</DateExitedSchool>
<EnrollmentStatusTypeId>4</EnrollmentStatusTypeId>
<NumDaysAttended>25.0</NumDaysAttended>
<NumDaysEnrolled>21.1</NumDaysEnrolled>
<NumUnexcusedAbsences>5</NumUnexcusedAbsences>
<IsSchoolChoice>2</IsSchoolChoice>
<IsLAPReading>3</IsLAPReading>
<IsLAPMath>4</IsLAPMath>
<IsTASReading>5</IsTASReading>
<IsTASMath>6</IsTASMath>
<IsTitleIMigrant>7</IsTitleIMigrant>
<IsSection504>8</IsSection504>
<Is21stCentury>9</Is21stCentury>
<IsSupplementalServices>0</IsSupplementalServices>
<IsGifted>1</IsGifted>
<IsBilingualProgram>2</IsBilingualProgram>
<IsSpecialEd>3</IsSpecialEd>
<SpecialEdLREPartATypeId>1</SpecialEdLREPartATypeId>
<SpecialEdLREPartBTypeId>3</SpecialEdLREPartBTypeId>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
<APCourses>
<APCourse>1</APCourse>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
</APCourses>
</School>
</Student>
</SSID>

Wednesday, March 21, 2012

Refering to a parent element with openxml

Refering to a parent element with openxml

Referencing xs:schema within a SCHEMA COLLECTION

Referencing xs:schema within a SCHEMA COLLECTION

Monday, March 12, 2012

Reference to an undefined name !

Reference to an undefined name !

Friday, March 9, 2012

Reference ID problem for import to SQL

reduce databse size

Blog Archive

About Me