Need Source for Information_Schema Views

I need the source definitions for the Information_Schema.Routines view and
would probably find a use for other view sources from time to time. Can
anyone point me to these?
I am trying to reverse engineer a poorly documented SQL Server 2000 DB with
EXTENSIVE business logic in hundreds of sometimes large SPs. The
Information_Schema.Routines view only allows 4000 characters for the routine
definition column (the source code for the procedure) and I have lots and
lots of SPs over that limit. I'm trying to create a report containing key
info about key procedures but I'm having content chopped off.
Thanks. Larry
--
Larry WestYou can find the source code for these views in the master database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry West|||Hi Larry
Can you clarify?
Is your need actually to get the definition of the
Information_Schema.Routines view, or is to find a way to display the text of
your own procedures that are are more than 4000 characters in length?
You can use sp_helptext 'my_proc_name' to get the full text of any
procedure. You can also use it to get the full text of any view, so you
could go to the master database and do this
exec sp_helptext 'information_schema.routines'
Or, in your own user databases, you could do this:
EXEC sp_helptext 'mybigprocedure'
Also note that in certain tools, like the Query Analyzer, the maximum number
of display characters is by default set to a small number. You would see the
full definition, even if it is available, if you don't go to
Tools|Options|Results and change the value of Maximum characters per column.
--
HTH
Kalen Delaney, SQL Server MVP
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB
> with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the
> routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry Westsql

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I need to get my knowledge about indexes expanded a little bit...
>> What's the best/easiest way to view Indexes and their definition in
>> SQL server. I can look in sysindexes, but as I see it it only shows
>> me the different indexes by name, but not so much about the
>> definition etc. (or maybe I just don't know what to look for). I can
>> also use sp_helpindex, but that only shows me very little as well. I
>> know I can look up the difinition in EM, but that's a bit cumbersome
>> to click through all tables to see it. Reason for asking is that I
>> have 2 almost identical databases, where on one of them there's a
>> number of indexes defined. These Indexes I'd like to create in the
>> second database as well.
>> Best Regards
>> Steen Persson

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen Persson
Steen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need most recent record from views.

I'm working with a report that uses three views. There are duplicate records because the 'priority' which comes from one view has changed and SELECT DISTINCT sees it as a separate record. The users only want the latest record with the changed 'priority'. A second view contains an audit datetime stamp and a third view contains additional fields needed. Is it possible to get the MAX datetime from the second view, thereby getting the latest 'priority' from the linked views? I've tried to SELECT MAX(audit_datetime) and also coded it in the WHERE clause but SQL does not like that. I assume it's because there are a number of fields in the SELECT.could you please send us the query in order to check the code, it should be working as you say, but maybe the code has a syntax error.

Need help writing a custom View in Sql Server

Does anyone knowif the following sql view is possible to write and execute as a view script?

**********find employee matching the given UserID*************
SELECT * FROM Employees WHERE EmployeeID=@.UserID

***********find client matching the given ClientID**********
SELECT *FROM Clients WHERE ClientID=@.ClientID

**********findall contacts and events associated with ClientID*********
SELECT *FROM Contacts WHERE Contact.ClientID=@.ClientID
SELECT *FROM Events WHERE Event.ClientID=@.ClientID

*********selectall audits with Key values matching the primary keys of each client, contact orevent*********
SELECT *FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)

I basicallyneed to find a employee based on its ID. Then I need to find any records from the table Auditwith Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?

You need list all your table's schema. If they are related, you can write a query with JOINs and put into a view.|||Well I don't want to JOIN them. I just want to select certain items out of the Audit table based on the key values taken from records retrieved from Clients, Contacts and Events. I just need to return records matching the schema in Audit, nothing else.|||

You could do something like this: If this is not what you are looking for you need to detail the structure of each of the tables and the columns that would be used in the [Key] column in the Audit table.

Declare @.table table (KeyIdint)INSERT INTO @.table SELECT <column>FROM EmployeesWHERE EmployeeId = @.UserIdINSERT INTO @.table SELECT <column>FROM ClientsWHERE ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM ContactsWHERE Contact.ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM EventsWHERE ClientID=@.ClientIDSELECT *FROM AuditWHERE [Key]In (SELECT KeyIdFROM @.table)

|||

You can format and execute a string similar to:

CREATE VIEW MyView as Select * from MyTable where ID = 55

But the view will be hardcoded to the ID of 55...probably not very useful.

A view itself cannot take parameters but I read that you can do it with user-defined functions.

|||

If you need to pass a parameter to it you need to use a stored procedure, not a view.

Need Help with XML Schema for Bulkload

I've been struggling for days trying to create an XML View/Schema mapped to
SQL Server to import a relatively straightforward XML document into a simple
(only two tables) SQL Server database. The examples I've seen do not seem to
address cases where some XML elements do not contribute to the population of
the database. And I keep getting errors "needs a relationship" even when I
add relationships. I also want SQL Server to handle the identity columns and
so I set KeepIdentity to False.
So, can anyone help or direct me to examples that could help me understand
the issues. I'm Googled out. Thanks.
XML Document
<pregnancies>
<pregnancy>
<summary>
<firstName>Jane</firstName>
<lastName>Doe</lastName>
<dob>7/22/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Brown</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Brown</factValue>
</fact>
</facts>
</pregnancy>
<pregnancy>
<summary>
<firstName>Mary</firstName>
<lastName>Smith</lastName>
<dob>6/12/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Blue</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Blonde</factValue>
</fact>
</facts>
</pregnancy>
</pregnancies>
Target Database Tables
Pregnancies
PregnancyID
FirstName
LastName
DateOfBirth
PregnancyFacts
PregnancyFactID
PregnancyID (FK)
FactDate
FactName
FactValue
I ran across this on the web
(http://www.topxml.com/sqlxml/using_sqlxmladapter_in_dotnet.asp)
"On the negative side Bulk Load also cannot handle nested types where
children require the IDENTITY of the parent as a foreign key."
I think this is exactly what I was trying to do. For each <Pregnancy>,
automatically generate a PregnancyID identity column, and then use that
PregnancyID in all the PregnancyFacts associated with that pregnancy.
I chose SQLXML Bulkload because I am trying to import millions of elements
into a database.
Is the statement above true for v4.0? How else can I import very large XML
files and tag all PregnancyFacts with a single PregnancyID for each
pregnancy?
Thanks for any help and direction.
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've been struggling for days trying to create an XML View/Schema mapped
> to SQL Server to import a relatively straightforward XML document into a
> simple (only two tables) SQL Server database. The examples I've seen do
> not seem to address cases where some XML elements do not contribute to the
> population of the database. And I keep getting errors "needs a
> relationship" even when I add relationships. I also want SQL Server to
> handle the identity columns and so I set KeepIdentity to False.
> So, can anyone help or direct me to examples that could help me understand
> the issues. I'm Googled out. Thanks.
> XML Document
> --
> <pregnancies>
> <pregnancy>
> <summary>
> <firstName>Jane</firstName>
> <lastName>Doe</lastName>
> <dob>7/22/85</dob>
> </summary>
> <facts>
> <fact>
> <factDate>3/3/07</factDate>
> <factName>Eye Color</factName>
> <factValue>Brown</factValue>
> </fact>
> <fact>
> <factDate>6/6/07</factDate>
> <factName>Hair Color</factName>
> <factValue>Brown</factValue>
> </fact>
> </facts>
> </pregnancy>
> <pregnancy>
> <summary>
> <firstName>Mary</firstName>
> <lastName>Smith</lastName>
> <dob>6/12/85</dob>
> </summary>
> <facts>
> <fact>
> <factDate>3/3/07</factDate>
> <factName>Eye Color</factName>
> <factValue>Blue</factValue>
> </fact>
> <fact>
> <factDate>6/6/07</factDate>
> <factName>Hair Color</factName>
> <factValue>Blonde</factValue>
> </fact>
> </facts>
> </pregnancy>
> </pregnancies>
> Target Database Tables
> --
> Pregnancies
> --
> PregnancyID
> FirstName
> LastName
> DateOfBirth
> PregnancyFacts
> --
> PregnancyFactID
> PregnancyID (FK)
> FactDate
> FactName
> FactValue
>
|||Hello,
I came up with the following schema based on your xml data:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="PPF"
parent="Pregnancies"
child="PregnancyFacts"
parent-key="PregnancyID"
child-key="PregnancyID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="pregnancies" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="pregnancy" sql:relation="Pregnancies">
<xs:complexType>
<xs:sequence>
<xs:element name="summary" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="firstName" sql:field="FirstName"
type="xs:string"/>
<xs:element name="lastName" sql:field="LastName" type="xs:string"/>
<xs:element name="dob" sql:field="DateOfBirth" type="xs:date"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="facts" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="fact" sql:relation="PregnancyFacts"
sql:relationship="PPF">
<xs:complexType>
<xs:sequence>
<xs:element name="factDate" sql:field="FactDate" type="xs:date"/>
<xs:element name="factName" sql:field="FactName"
type="xs:string"/>
<xs:element name="factValue" sql:field="FactValue"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
When I bulkload using this schema and the xml data below I got the folloing
inserted in the tables:
PregnancyID FirstName LastName DateOfBirth
-- -- -- --
1 Jane Doe 1985-07-22 00:00:00.000
2 Mary Smith 1985-06-12 00:00:00.000
(2 row(s) affected)
PregnancyFactID PregnancyID FactDate FactName FactValue
-- -- -- -- --
1 1 2007-03-03 00:00:00.000 Eye Color Brown
2 1 2007-06-06 00:00:00.000 Hair Color Brown
3 2 2007-03-03 00:00:00.000 Eye Color Blue
4 2 2007-06-06 00:00:00.000 Hair Color Blonde
(4 row(s) affected)
Identity values will be propagated from the parent to the child tables.
I hope this solves your problem.
Best regards,
Monica Frintu
"Don Miller" wrote:

> I ran across this on the web
> (http://www.topxml.com/sqlxml/using_sqlxmladapter_in_dotnet.asp)
> "On the negative side Bulk Load also cannot handle nested types where
> children require the IDENTITY of the parent as a foreign key."
> I think this is exactly what I was trying to do. For each <Pregnancy>,
> automatically generate a PregnancyID identity column, and then use that
> PregnancyID in all the PregnancyFacts associated with that pregnancy.
> I chose SQLXML Bulkload because I am trying to import millions of elements
> into a database.
> Is the statement above true for v4.0? How else can I import very large XML
> files and tag all PregnancyFacts with a single PregnancyID for each
> pregnancy?
> Thanks for any help and direction.
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
>
>

Need Help with XML Schema for Bulkload

I've been struggling for days trying to create an XML View/Schema mapped to
SQL Server to import a relatively straightforward XML document into a simple
(only two tables) SQL Server database. The examples I've seen do not seem to
address cases where some XML elements do not contribute to the population of
the database. And I keep getting errors "needs a relationship" even when I
add relationships. I also want SQL Server to handle the identity columns and
so I set KeepIdentity to False.
So, can anyone help or direct me to examples that could help me understand
the issues. I'm Googled out. Thanks.
XML Document
--
<pregnancies>
<pregnancy>
<summary>
<firstName>Jane</firstName>
<lastName>Doe</lastName>
<dob>7/22/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Brown</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Brown</factValue>
</fact>
</facts>
</pregnancy>
<pregnancy>
<summary>
<firstName>Mary</firstName>
<lastName>Smith</lastName>
<dob>6/12/85</dob>
</summary>
<facts>
<fact>
<factDate>3/3/07</factDate>
<factName>Eye Color</factName>
<factValue>Blue</factValue>
</fact>
<fact>
<factDate>6/6/07</factDate>
<factName>Hair Color</factName>
<factValue>Blonde</factValue>
</fact>
</facts>
</pregnancy>
</pregnancies>
Target Database Tables
--
Pregnancies
--
PregnancyID
FirstName
LastName
DateOfBirth
PregnancyFacts
--
PregnancyFactID
PregnancyID (FK)
FactDate
FactName
FactValueI ran across this on the web
(http://www.topxml.com/sqlxml/using_...r_in_dotnet.asp)
"On the negative side Bulk Load also cannot handle nested types where
children require the IDENTITY of the parent as a foreign key."
I think this is exactly what I was trying to do. For each <Pregnancy>,
automatically generate a PregnancyID identity column, and then use that
PregnancyID in all the PregnancyFacts associated with that pregnancy.
I chose SQLXML Bulkload because I am trying to import millions of elements
into a database.
Is the statement above true for v4.0? How else can I import very large XML
files and tag all PregnancyFacts with a single PregnancyID for each
pregnancy?
Thanks for any help and direction.
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've been struggling for days trying to create an XML View/Schema mapped
> to SQL Server to import a relatively straightforward XML document into a
> simple (only two tables) SQL Server database. The examples I've seen do
> not seem to address cases where some XML elements do not contribute to the
> population of the database. And I keep getting errors "needs a
> relationship" even when I add relationships. I also want SQL Server to
> handle the identity columns and so I set KeepIdentity to False.
> So, can anyone help or direct me to examples that could help me understand
> the issues. I'm Googled out. Thanks.
> XML Document
> --
> <pregnancies>
> <pregnancy>
> <summary>
> <firstName>Jane</firstName>
> <lastName>Doe</lastName>
> <dob>7/22/85</dob>
> </summary>
> <facts>
> <fact>
> <factDate>3/3/07</factDate>
> <factName>Eye Color</factName>
> <factValue>Brown</factValue>
> </fact>
> <fact>
> <factDate>6/6/07</factDate>
> <factName>Hair Color</factName>
> <factValue>Brown</factValue>
> </fact>
> </facts>
> </pregnancy>
> <pregnancy>
> <summary>
> <firstName>Mary</firstName>
> <lastName>Smith</lastName>
> <dob>6/12/85</dob>
> </summary>
> <facts>
> <fact>
> <factDate>3/3/07</factDate>
> <factName>Eye Color</factName>
> <factValue>Blue</factValue>
> </fact>
> <fact>
> <factDate>6/6/07</factDate>
> <factName>Hair Color</factName>
> <factValue>Blonde</factValue>
> </fact>
> </facts>
> </pregnancy>
> </pregnancies>
> Target Database Tables
> --
> Pregnancies
> --
> PregnancyID
> FirstName
> LastName
> DateOfBirth
> PregnancyFacts
> --
> PregnancyFactID
> PregnancyID (FK)
> FactDate
> FactName
> FactValue
>|||Hello,
I came up with the following schema based on your xml data:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="PPF"
parent="Pregnancies"
child="PregnancyFacts"
parent-key="PregnancyID"
child-key="PregnancyID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="pregnancies" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="pregnancy" sql:relation="Pregnancies">
<xs:complexType>
<xs:sequence>
<xs:element name="summary" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="firstName" sql:field="FirstName"
type="xs:string"/>
<xs:element name="lastName" sql:field="LastName" type="xs:string"/>
<xs:element name="dob" sql:field="DateOfBirth" type="xs:date"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="facts" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="fact" sql:relation="PregnancyFacts"
sql:relationship="PPF">
<xs:complexType>
<xs:sequence>
<xs:element name="factDate" sql:field="FactDate" type="xs:date"/>
<xs:element name="factName" sql:field="FactName"
type="xs:string"/>
<xs:element name="factValue" sql:field="FactValue"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
When I bulkload using this schema and the xml data below I got the folloing
inserted in the tables:
PregnancyID FirstName LastName DateOfBirth
-- -- -- --
1 Jane Doe 1985-07-22 00:00:00.000
2 Mary Smith 1985-06-12 00:00:00.000
(2 row(s) affected)
PregnancyFactID PregnancyID FactDate FactName FactValue
-- -- -- -- --
1 1 2007-03-03 00:00:00.000 Eye Color Brown
2 1 2007-06-06 00:00:00.000 Hair Color Brown
3 2 2007-03-03 00:00:00.000 Eye Color Blue
4 2 2007-06-06 00:00:00.000 Hair Color Blonde
(4 row(s) affected)
Identity values will be propagated from the parent to the child tables.
I hope this solves your problem.
Best regards,
Monica Frintu
"Don Miller" wrote:

> I ran across this on the web
> (http://www.topxml.com/sqlxml/using_...r_in_dotnet.asp)
> "On the negative side Bulk Load also cannot handle nested types where
> children require the IDENTITY of the parent as a foreign key."
> I think this is exactly what I was trying to do. For each <Pregnancy>,
> automatically generate a PregnancyID identity column, and then use that
> PregnancyID in all the PregnancyFacts associated with that pregnancy.
> I chose SQLXML Bulkload because I am trying to import millions of elements
> into a database.
> Is the statement above true for v4.0? How else can I import very large XML
> files and tag all PregnancyFacts with a single PregnancyID for each
> pregnancy?
> Thanks for any help and direction.
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Oiyb6DynHHA.4412@.TK2MSFTNGP02.phx.gbl...
>
>

need help with unique SQL data view

The objective here is to create a SQL data view of two tables that have a on
e
to many relationship. The two tables represent a simple category/subcategor
y
relationship and the common value that will need to be returned will be the
name and its primary key (unique identifier). Let me represent the desired
result by the following example.
This represents the table/field schema:
TABLE 1 – CATEGORY
category_id
name
TABLE 2 – SUBCATEGORY
category_id
name
parent_id (relates to category_id in TABLE 1- CATEGORY)
The following represents the top level CATEGORY sample data:
TABLE 1 – CATEGORY
Name
Foundation
Slab
Rough Shell
Mechanicals
Finish Trades
Appliances
The second representation is the related SUBCATEGORY sample data:
TABLE 1 – SUBCATEGORY
Parent_Name Name
Rough Shell Framing
Rough Shell Windows
Rough Shell Doors
Appliances Kitchen
Appliances Laundry
(Note: The other top level categories do not have subcategories defined)
What is required of the SQL data view is to provide both name and
category_id for each distinct CATEGORY and SUBCATEGORY (see example below).
That specifically requires that each top level CATEGORY be listed explicitly
,
and each CATEGORY that does have an associated SUBCATEGORY will also need to
be listed (with names concatenated with hyphen as example below) both withou
t
its associated SUBCATEGORY records and with the associated SUBCATEGORY
records (along with its specific category_id unique identifier). Using the
two datasets above the required output of the data view would be as follows:
Concatenated Name Category_id
Foundation {QDEP74849393939303020}
Slab {BNIO802274939302020207}
Rough Shell {UUED34128554488326264} ? (Note here the top level category
is
listed in addition to its related records)
Rough Shell – Framing {HGKJ556388920118933933}
Rough Shell – Windows {LLPU112331099789875542}
Rough Shell – Doors {YVXX777546752211456023}
Mechanicals {DSFD44322274747299844}
Finish Trades {SSWQ43271727300998112}
Appliances {MYYB986373722924848433}
(Note here the top level category
is listed in addition to its related records)
Appliances – Kitchen {EEIU009833551238700909}
Appliances – Laundry (VVVC032327659302846593}
Thanks for your help.
Regards,
DonTest this:
SELECT name, category_id FROM Category
UNION
(SELECT LTRIM(RTRIM(a.name))+' - '+LTRIM(RTRIM(b.name)) AS name, b.category_
id
FROM Category a JOIN Subcategory b ON a.category_id = b.parent_id)
ORDER BY name
"dbj" wrote:

> The objective here is to create a SQL data view of two tables that have a
one
> to many relationship. The two tables represent a simple category/subcateg
ory
> relationship and the common value that will need to be returned will be th
e
> name and its primary key (unique identifier). Let me represent the desire
d
> result by the following example.
> This represents the table/field schema:
> TABLE 1 – CATEGORY
> category_id
> name
> TABLE 2 – SUBCATEGORY
> category_id
> name
> parent_id (relates to category_id in TABLE 1- CATEGORY)
>
> The following represents the top level CATEGORY sample data:
> TABLE 1 – CATEGORY
> Name
> Foundation
> Slab
> Rough Shell
> Mechanicals
> Finish Trades
> Appliances
> The second representation is the related SUBCATEGORY sample data:
> TABLE 1 – SUBCATEGORY
> Parent_Name Name
> Rough Shell Framing
> Rough Shell Windows
> Rough Shell Doors
> Appliances Kitchen
> Appliances Laundry
> (Note: The other top level categories do not have subcategories defined)
> What is required of the SQL data view is to provide both name and
> category_id for each distinct CATEGORY and SUBCATEGORY (see example below)
.
> That specifically requires that each top level CATEGORY be listed explicit
ly,
> and each CATEGORY that does have an associated SUBCATEGORY will also need
to
> be listed (with names concatenated with hyphen as example below) both with
out
> its associated SUBCATEGORY records and with the associated SUBCATEGORY
> records (along with its specific category_id unique identifier). Using th
e
> two datasets above the required output of the data view would be as follow
s:
> Concatenated Name Category_id
> Foundation {QDEP74849393939303020}
> Slab {BNIO802274939302020207}
> Rough Shell {UUED34128554488326264} ? (Note here the top level categor
y is
> listed in addition to its related records)
> Rough Shell – Framing {HGKJ556388920118933933}
> Rough Shell – Windows {LLPU112331099789875542}
> Rough Shell – Doors {YVXX777546752211456023}
> Mechanicals {DSFD44322274747299844}
> Finish Trades {SSWQ43271727300998112}
> Appliances {MYYB986373722924848433}
(Note here the top level catego
ry
> is listed in addition to its related records)
> Appliances – Kitchen {EEIU009833551238700909}
> Appliances – Laundry (VVVC032327659302846593}
> Thanks for your help.
> Regards,
> Don
>|||Worked like a charm. Thanks very much!!!
Don
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:52BB0F34-E93A-40BC-BB75-B9A9A765B52F@.microsoft.com...
> Test this:
> SELECT name, category_id FROM Category
> UNION
> (SELECT LTRIM(RTRIM(a.name))+' - '+LTRIM(RTRIM(b.name)) AS name,
> b.category_id
> FROM Category a JOIN Subcategory b ON a.category_id = b.parent_id)
> ORDER BY name
>
> "dbj" wrote:
>