Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating an XML file

Status
Not open for further replies.
mflancour

mflancour

MIS
Apr 23, 2002
379
US
Following are 2 examples of records that I need to produce for an xml file from a table in sql. How do I create this file?

<Item>
<Warehouse>FG</Warehouse>
<StockCode>A100</StockCode>
<Version />
<Release />
<Date>2006-10-31</Date>
<Quantity>100</Quantity>
<UnitOfMeasure />
<Units />
<Pieces />
<CostValue>305.00</CostValue>
<SalesValue>552.80</SalesValue>
<TransactionCount />
</Item>
<Item>
<Warehouse>FG</Warehouse>
<StockCode>A100</StockCode>
<Version />
<Release />
<Date>2005-11-30</Date>
<Quantity>100</Quantity>
<UnitOfMeasure />
<Units />
<Pieces />
<CostValue>305.00</CostValue>
<SalesValue>552.80</SalesValue>
<TransactionCount />
</Item>
 
Sort by date Sort by votes
Code: 
Select Field1, Field2, etc...
From   Table
[!]For XML auto, elements[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Thanks! Looks pretty easy. One more followup question, the following fields are needed in the xml file, but are not in either table:
<UnitOfMeasure />
<Units />
<Pieces />
How do I get the xml file to include these as shown above?
 
Upvote 0 Downvote
Code: 
Select Field1, 
       '' As UnitOfMeasure,
       '' As AnyOldFieldName,
       Field2, etc...
From   Table
For XML auto, elements

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Receiving the following error:
Server: Msg 170, Level 15, State 1, Procedure VIEW1, Line 10
Line 10: Incorrect syntax near 'XML'.
'''''''''''''''''''

SELECT dbo.WarehouseXRef.SysproWhse AS Warehouse, dbo.TrimmedTrend.TrimmedProdID AS Stockcode, '' AS Version, '' AS Release,
dbo.TrimmedTrend.InvoiceDate AS Date, dbo.TrimmedTrend.QtyShip AS Quantity, '' AS UnitOfMeasure, '' AS Units, '' AS Pieces, '' AS CostValue,
'' AS SalesValue, '' AS TransactionCount
FROM dbo.TrimmedSyspro INNER JOIN
dbo.TrimmedTrend ON dbo.TrimmedSyspro.TrimmedStockcode = dbo.TrimmedTrend.TrimmedProdID LEFT OUTER JOIN
dbo.WarehouseXRef ON dbo.TrimmedTrend.WhseId = dbo.WarehouseXRef.TrendWhse
For XML auto, elements

 
Upvote 0 Downvote
According to books on line...

Books on line said:
FOR XML is not valid in a SELECT statement used in a view definition or in a user-defined function that returns a rowset. For example, this statement is not allowed:
Click to expand...

So... your options are to create a stored procedure to do this, or create the view without the XML stuff on it, and select from the view with the 'for xml' clause.

ex.

Select * From ViewName For XML Auto, Elements



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Same error:
Server: Msg 170, Level 15, State 1, Procedure VIEW2, Line 7
Line 7: Incorrect syntax near 'xml'.


SELECT dbo.VIEW1.*
FROM dbo.VIEW1
FOR xml auto, elements
 
Upvote 0 Downvote
Server: Msg 170, Level 15, State 1, Procedure [!]VIEW2[/!], Line 7

You still are trying to create a view that returns XML from it.

Try...

Code: 
Create Procedure GetXMLStuff
As
SELECT     dbo.VIEW1.*
FROM         dbo.VIEW1 
FOR xml auto, elements

Then run it with...

Exec GetXMLStuff


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
That depends on where you are running the query from. Is it Query Analyzer? if so... Press CTRL-SHIFT-F and then run the query. It will prompt for a file name, and done.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Sweet. Discovered what hopfully will be the last obsticle. How does the xml know what warehouse goes to what record? In the following example you see <item> before each record. Is there any way to do this?

<Item>
<Warehouse>FG</Warehouse>
<StockCode>A100</StockCode>
<Version />
<Release />
<Date>2006-10-31</Date>
<Quantity>100</Quantity>
<UnitOfMeasure />
<Units />
<Pieces />
<CostValue>305.00</CostValue>
<SalesValue>552.80</SalesValue>
<TransactionCount />
</Item>
<Item>
<Warehouse>FG</Warehouse>
<StockCode>A100</StockCode>
<Version />
<Release />
<Date>2005-11-30</Date>
<Quantity>100</Quantity>
<UnitOfMeasure />
<Units />
<Pieces />
<CostValue>305.00</CostValue>
<SalesValue>552.80</SalesValue>
<TransactionCount />
</Item>
 
Upvote 0 Downvote
The 'item' part corresponds to the table/view name. To change it...

Code: 
Alter Procedure GetXMLStuff
As
SELECT     Item.*
FROM         dbo.VIEW1 As Item
FOR xml auto, elements

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
ok, everything works...except..

When I try to open the file however, I recieve an error. It appears to be cutting off some of the data.
 
Upvote 0 Downvote
How are you putting the data in to a file?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I use ctrl+shift+f
then when it asks me where to save the file I save as filename.xml.

The strange thing is that even when I view the data in query ananyzer it is cuttin off text.
 
Upvote 0 Downvote
In Query Analyzer...

Click tools -> Options
click the 'results' tab

What do you have for 'Maximum characters per column'?

Try changing this value to something larger.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
was at 256, changed it to 1000. Still cutting off info just farther out.
 
Upvote 0 Downvote
Try setting it to 8000.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
736
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
601
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
301
grnzbra
grnzbra
ahmedsa2018
  • Locked
  • Question
How to change STRING_AGG to stuff xml to split feature value by pip?
Replies
0
Views
291
ahmedsa2018
ahmedsa2018
Auguy
  • Locked
  • Question
Connection String in .NET program Config File 2
Replies
10
Views
679
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top