Generating queries - 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

General SQL Server Forums

New to SQL Server Programming

Generating queries

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-20 : 11:42:18

Hi friends My XML structure is:
Production.ProductModel ProductModelID="1" Name="Classic Vest" rowguid="29321D47-1E4C-4AAC-887C-19634328C25E" ModifiedDate="2003-06-01T00:00:00" />
<Production.ProductModel ProductModelID="2" Name="Cycling Cap" rowguid="474FB654-3C96-4CB9-82DF-2152EEFFBDB0" ModifiedDate="2001-06-01T00:00:00" />
<Production.ProductModel ProductModelID="3" Name="Full-Finger Gloves" rowguid="A75483FE-3C47-4AA4-93CF-664B51192987" ModifiedDate="2002-06-01T00:00:00" />
<Production.ProductModel ProductModelID="4" Name="Half-Finger Gloves" rowguid="14B56F2A-D4AA-40A4-B9A2-984F165ED702" ModifiedDate="2002-06-01T00:00:00" />
<Production.ProductModel ProductModelID="5" Name="HL Mountain Frame" rowguid="FDD5407B-C2DB-49D1-A86B-C13A2E3582A2" ModifiedDate="2001-06-01T00:00:00" />
<Production.ProductModel ProductModelID="6" Name="HL Road Frame" rowguid="4D332ECC-48B3-4E04-B7E7-227F3AC2A7EC" ModifiedDate="1998-05-02T00:00:00" />
<Production.ProductModel ProductModelID="7" Name="HL Touring Frame" rowguid="D60ED2A5-C100-4C54-89A1-531404C4A20F" ModifiedDate="2005-05-16T16:34:28.980">
<Instructions>
<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
Adventure Works CyclesFR-210B Instructions for Manufacturing HL Touring Frame. Summary: This document contains manufacturing instructions for manufacturing the HL Touring Frame, Product Model 7. Instructions are work center specific and are identified by Work Center ID. These instructions must be followed in the order presented. Deviation from the instructions is not permitted unless an authorized Change Order detailing the deviation is provided by the Engineering Manager.<Location LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10">
Work Center 10 - Frame Forming. The following instructions pertain to Work Center 10. (Setup hours = .5, Labor Hours = 2.5, Machine Hours = 3, Lot Sizing = 100)<step>
Insert <material>aluminum sheet MS-2341</material> into the <tool>T-85A framing tool</tool>.
</step><step>
Attach <tool>Trim Jig TJ-26</tool> to the upper and lower right corners of the aluminum sheet.
</step><step>
Using a <tool>router with a carbide tip 15</tool>, route the aluminum sheet following the jig carefully.
</step><step>
Insert the frame into <tool>Forming Tool FT-15</tool> and press Start.
</step><step>
When finished, inspect the forms for defects per Inspection Specification <specs>INFS-111</specs>.
</step><step>Remove the frames from the tool and place them in the Completed or Rejected bin as appropriate.</step></Location><Location LaborHours="1.75" LotSize="1" MachineHours="2" SetupHours="0.15" LocationID="20">
Work Center 20 - Frame Welding. The following instructions pertain to Work Center 20. (Setup hours = .15, Labor Hours = 1.75, Machine Hours = 2, Lot Sizing = 1)<step>
Assemble all frame components following blueprint <blueprint>1299</blueprint>.
</step><step>
Weld all frame components together as shown in illustration <diag>3</diag></step><step>
Inspect all weld joints per Adventure Works Cycles Inspection Specification <specs>INFS-208</specs>.
</step></Location><Location LaborHours="1" LotSize="1" LocationID="30">
Work Center 30 - Debur and Polish. The following instructions pertain to Work Center 30. (Setup hours = 0, Labor Hours = 1, Machine Hours = 0, Lot Sizing = 1)<step>
Using the <tool>standard debur tool</tool>, remove all excess material from weld areas.
</step><step>
Using <material>Acme Polish Cream</material>, polish all weld areas.
</step></Location><Location LaborHours="0.5" LotSize="20" MachineHours="0.65" LocationID="45">
Work Center 45 - Specialized Paint. The following instructions pertain to Work Center 45. (Setup hours = 0, Labor Hours = .5, Machine Hours = .65, Lot Sizing = 20)<step>
Attach <material>a maximum of 20 frames</material> to <tool>paint harness</tool> ensuring frames are not touching.
</step><step>
Mix <material>primer PA-529S</material>. Test spray pattern on sample area and correct flow and pattern as required per engineering spec <specs>AWC-501</specs>.
</step><step>Apply thin coat of primer to all surfaces. </step><step>After 30 minutes, touch test for dryness. If dry to touch, lightly sand all surfaces. Remove all surface debris with compressed air. </step><step>
Mix <material>paint</material> per manufacturer instructions.
</step><step>
Test spray pattern on sample area and correct flow and pattern as required per engineering spec <specs>AWC-509</specs>.
</step><step>Apply thin coat of paint to all surfaces. </step><step>After 60 minutes, touch test for dryness. If dry to touch, reapply second coat. </step><step>
Allow paint to cure for 24 hours and inspect per <specs>AWC-5015</specs>.
</step></Location><Location LaborHours="3" LotSize="1" SetupHours="0.25" LocationID="50">
Work Center 50 - SubAssembly. The following instructions pertain to Work Center 50. (Setup hours = .25, Labor Hours = 3, Machine Hours = 0, Lot Sizing = 1)<step>Add Seat Assembly. </step><step>Add Brake assembly. </step><step>Add Wheel Assembly. </step><step>Inspect Front Derailleur. </step><step>Inspect Rear Derailleur. </step></Location><Location LaborHours="4" LotSize="1" LocationID="60">
Work Center 60 - Final Assembly. The following instructions pertain to Work Center 60. (Setup hours = 0, Labor Hours = 4, Machine Hours = 0, Lot Sizing = 1)<step>
Perform final inspection per engineering specification <specs>AWC-915</specs>.
</step><step>Complete all required certification forms.</step><step>Move to shipping.</step></Location></root>
</Instructions>

Now I want to generate a list of all types of bicycles that go through Work Center 10.

I tried doing it in the following way:

select Instructions.query('declare namespace ns =
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
data(/ns:root/ns:Location[1]/ns:workcenter[1].Prefix)') AS Information
FROM Production.ProductModel

I m getting the followin error:

Msg 2217, Level 16, State 1, Line 3
XQuery [Production.ProductModel.Instructions.query()]: ',' or ')' expected

What is the problem?
Pls let me know the correct query used to generate the desired list.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:46:25

i cant see a node named workcenter in xml posted

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-20 : 16:04:37

See in booksonline for Xquery. There are numerous examples with AdventureWorks.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-22 : 02:22:55

quote:
Originally posted by visakh16

i cant see a node named workcenter in xml posted

Yes that is what I have doubt about retrieving it.

ACtually I am a student and I have been given this exercise to be performed.

Though it does not contain any nodes named as WorkCenter, It has the information about the work centre that is after the location node approximately 22nd line. Based on that information only we have to find it?

Pls help me soon...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:02:10

but thats just part of data inside location node

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-22 : 09:33:52

quote:
Originally posted by sital

quote:
Originally posted by visakh16

i cant see a node named workcenter in xml posted

Yes that is what I have doubt about retrieving it.

ACtually I am a student and I have been given this exercise to be performed.

Though it does not contain any nodes named as WorkCenter, It has the information about the work centre that is after the location node approximately 22nd line. Based on that information only we have to find it?

Pls help me soon...

What exactly you want to retrieve? Post Correct XML Structure from Production.ProductModel.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-22 : 11:43:41

quote:
Originally posted by visakh16

but thats just part of data inside location node

Ya I need to retrieve that part only.

How can I retrieve it?

Pls help me to generate the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:51:03

use exists() function. have a look at syntax in books online

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-22 : 11:53:06

Here is the exact example that your teacher gave:

http://msdn.microsoft.com/en-us/library/ms188282.aspx

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-26 : 23:02:48

quote:
Originally posted by sodeep

Here is the exact example that your teacher gave:

http://msdn.microsoft.com/en-us/library/ms188282.aspx

Thanks for giving me the link.
But I m not getting the query which I require. Can you please post the query?

Its Urgent!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 23:30:56

as suggested look for exists() in books online.make a try and post if you face any difficulty

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-27 : 06:35:22

quote:
Originally posted by visakh16

as suggested look for exists() in books online.make a try and post if you face any difficulty

Hi
I got the idea of retrieving. Its based on the LocationID we can retrieve it. I tried to use the following query but it is giving an error.

select Instructions.exist('(/root/Location/@LocationID="10")[1]') from Production.ProductModel

Error Message:
Msg 2260, Level 16, State 1, Line 1
XQuery [Production.ProductModel.Instructions.exist()]: There is no element named 'root'

Second query I tried is:
select ProductModelID from Production.ProductModel where Instructions.value('(/Location/LocationID)[1]','varchar(100)')= "10"

Error Message:

Msg 2260, Level 16, State 1, Line 1
XQuery [Production.ProductModel.Instructions.value()]: There is no element named 'Location'

Can you please find out the error and let me know.

Please sir its urgent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 08:48:20

where's namespace declaration?

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-27 : 11:23:09

quote:
Originally posted by visakh16

where's namespace declaration?

Even with namespace I have tried it is giving me an error.
And I would like to know is it necessary to declare namespaces for retrieving all XML columns?

The Query I tried is:
select Instructions.value('declare namespace ab = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
(/ab:root/ab:Location[@LocationID]="10")[1]','varchar(100)')
from Production.ProductModel

Error Message:

Msg 9314, Level 16, State 1, Line 2
XQuery [Production.ProductModel.Instructions.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'ab{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions}:Location' within inferred type 'element(ab{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions}:Location,#anonymous) *'.

Can you identify any error in this query?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 00:38:12

[code]SELECT Instructions.value('
declare namespace ab="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
(/ab:root/ab:Location[1]/@LocationID)[1]','int') as Info
FROM Production.ProductModel
Where Instructions is not null[/code]

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-29 : 00:41:52

quote:

Originally posted by sodeep

SELECT Instructions.value('
  declare namespace ab="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
(/ab:root/ab:Location[1]/@LocationID)[1]','int') as Info
FROM Production.ProductModel
Where Instructions is not null

Thank you so much for your reply.
I tried it but I have a condition where the location Id is 10.

Can you please modify your query and provide me the query with the condition LocationID is 10?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 01:48:36

[code]
SELECT Instructions.value('
declare namespace ab="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
(/ab:root/ab:Location[1][@LocationID=10]/@LocationID)[1]','int') as Info
FROM Production.ProductModel
Where Instructions is not null

[/code]

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-30 : 03:50:21

quote:

Originally posted by visakh16


SELECT Instructions.value('
  declare namespace ab="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
(/ab:root/ab:Location[1][@LocationID=10]/@LocationID)[1]','int') as Info
FROM Production.ProductModel
Where Instructions is not null

When I execute the above query, it is also displaying NULL values. What should I do it rectify this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:36:17

do you have nodes with LocationID attribute value 10?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 13:10:45

Because that Position doesn't have Value 10 for @LocationID attribute.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources