When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's Homestead"><Products ProductName="Grandma's BoysenberrySpread" UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products ProductName="Northwoods Cranberry Sauce" UnitsInStock="6" UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
XML doesn't actually include indents and newlines - that's just a convenient
way to format it so we can read it more easily. Query Analyzer just displays
the XML stream that gets returned by the query - from an XML-aware client
application's point of view, this is fine; it's just not very
human-friendly! If you want to see it formatted you'd have to load it into
an XML editor that formats it for you (and bear in mind that FOR XML queries
return XML Fragments - not full documents, so there's no root element).
The good news is that the SQL Management Studio tool in SQL Server 2005
includes an XML editor, so you get the results of FOR XML queries nicely
formatted.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ron Sissons" <RSISSONS@.rcoe.k12.ca.us> wrote in message
news:%23h9ImsJhEHA.396@.TK2MSFTNGP12.phx.gbl...
When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai"
UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang"
UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup"
UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New
Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun
Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef
Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's
Homestead"><Products ProductName="Grandma's Boysenberry Spread"
UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's
Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products
ProductName="Northwoods Cranberry Sauce" UnitsInStock="6"
UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new
lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13"
UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning"
UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results
window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
|||Thank you very much!
I thought I was going crazy not understanding..in all the examples the wording is:
"results in the following format:
<Shippers>
<ShipperID>1</ShipperID>
<CompanyName>Speedy Express</CompanyName>
<Orders>
<OrderID>10249</OrderID>
..."
I thought I was missing something.
Ron Sissons, DBAInformation Technology ServicesRiverside County Office of Education3939 Thirteenth Street, Riverside, CA 92502-0868Telephone: (909) 826-6471; FAX: [909] 826-6451
Showing posts with label products. Show all posts
Showing posts with label products. Show all posts
Sunday, February 26, 2012
Friday, February 24, 2012
FOR INSERT Trigger
I've got 2 tables to collect orders. The ORDERS table collects the name,
totals etc. The DETAILS table collects the products. One product per each
row. When a customer places an order, their CType field may get populated
with QW. If CType = 'QW', With a trigger, I need to add a product to the
DETAILS table. The following doesn't work, but should give you an idea what
I need.
IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
INSERT INTO DETAILS
(OrderID, OrderNo, Description, Qty, PriceEach)
VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
How do I get this done? Obviously, I need to query the order that was just
placed. I also need to place the corresponding OrderID into DETAILS.
thanks!Try this:
CREATE TRIGGER Orders_TrigAddQWDetails
ON dbo.Orders
FOR INSERT
As
Insert DETAILS (OrderID, OrderNo,
Description, Qty, PriceEach)
Select OrderID,'SF','ServiceFee',1,4
From Inserted Where CType = 'QW'
"shank" wrote:
> I've got 2 tables to collect orders. The ORDERS table collects the name,
> totals etc. The DETAILS table collects the products. One product per each
> row. When a customer places an order, their CType field may get populated
> with QW. If CType = 'QW', With a trigger, I need to add a product to the
> DETAILS table. The following doesn't work, but should give you an idea wha
t
> I need.
>
> IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
> INSERT INTO DETAILS
> (OrderID, OrderNo, Description, Qty, PriceEach)
> VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
> How do I get this done? Obviously, I need to query the order that was just
> placed. I also need to place the corresponding OrderID into DETAILS.
> thanks!
>
>
totals etc. The DETAILS table collects the products. One product per each
row. When a customer places an order, their CType field may get populated
with QW. If CType = 'QW', With a trigger, I need to add a product to the
DETAILS table. The following doesn't work, but should give you an idea what
I need.
IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
INSERT INTO DETAILS
(OrderID, OrderNo, Description, Qty, PriceEach)
VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
How do I get this done? Obviously, I need to query the order that was just
placed. I also need to place the corresponding OrderID into DETAILS.
thanks!Try this:
CREATE TRIGGER Orders_TrigAddQWDetails
ON dbo.Orders
FOR INSERT
As
Insert DETAILS (OrderID, OrderNo,
Description, Qty, PriceEach)
Select OrderID,'SF','ServiceFee',1,4
From Inserted Where CType = 'QW'
"shank" wrote:
> I've got 2 tables to collect orders. The ORDERS table collects the name,
> totals etc. The DETAILS table collects the products. One product per each
> row. When a customer places an order, their CType field may get populated
> with QW. If CType = 'QW', With a trigger, I need to add a product to the
> DETAILS table. The following doesn't work, but should give you an idea wha
t
> I need.
>
> IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
> INSERT INTO DETAILS
> (OrderID, OrderNo, Description, Qty, PriceEach)
> VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
> How do I get this done? Obviously, I need to query the order that was just
> placed. I also need to place the corresponding OrderID into DETAILS.
> thanks!
>
>
Subscribe to:
Posts (Atom)