SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Result to XML		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 03/01/2012 :  10:04:18  Show Profile  Reply with Quote
I need to write my result set to an XML file.
My query looks like this:

SELECT 
ItemNo
,Description
,ItemStat
,SupplierID
,CategoryName 
,ReferenceNum1
ReferenceNum2
FROM ItemMaster

How would I go about that?

X002548
Not Just a Number

15586 Posts
Posted - 03/01/2012 :  10:47:25  Show Profile  Reply with Quote
Look up FOR XML in books online...

OR, you can create the XML Form yourself with literals concat with the data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47062 Posts
Posted - 03/01/2012 :  11:27:21  Show Profile  Reply with Quote
quote:
Originally posted by HenryFulmer

I need to write my result set to an XML file.
My query looks like this:

SELECT 
ItemNo
,Description
,ItemStat
,SupplierID
,CategoryName 
,ReferenceNum1
ReferenceNum2
FROM ItemMaster

How would I go about that?

Are you looking at specifice XML format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 05/25/2012 :  17:23:21  Show Profile  Reply with Quote
I have 2 tables:
ItemMaster table and ItemReference table.
The XML file that I need to generate looks like:

<AllItems>
  <Item>
    <ItemNo>21345</itemNumber>
    <Description>Text Description</Description>
    <ItemReference>
      <RefrenceNo1>abcd</RefrenceNo1>
      <RefrenceNo2>kl3er</RefrenceNo2>
    </ItemReference>
  </Item>
</AllItems>


My query so far looks like this, I am still missing the nesting of the reference number:

SELECT 
 ItemNo
,Description
FROM ItemMaster 
-->> join to Reference table
FOR xml path ('Item'),type , root ('AllParts')

My questions are:
How can I add the reference numbers to this query?
and
How can I have the xml result be exported to a network directory?

Any help would be appreciated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47062 Posts
Posted - 05/25/2012 :  19:42:00  Show Profile  Reply with Quote
yep...you can get reference details by including it as a subquery
You can export xml to network directory using bcp with query out option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 05/29/2012 :  13:16:52  Show Profile  Reply with Quote
would you mind pointing me into the right direction? How would I need to construct the subquery?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47062 Posts
Posted - 05/29/2012 :  14:35:33  Show Profile  Reply with Quote 

SELECT 
 ItemNo
,Description
(SELECT RefrenceNo1,RefrenceNo2 FROM ItemReference WHERE relatedcol= m.column FOR XML PATH('ItemReference'),type)
FROM ItemMaster m
FOR xml path ('Item'),type , root ('AllParts')




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 08/13/2012 :  14:22:02  Show Profile  Reply with Quote
Please allow me to post my sample data and queries:
Table1:

--create item master table
CREATE TABLE [dbo].[itemMaster](
	[ItemNumber] [int] NOT NULL,
	[ItemDescription] [varchar](15) NOT NULL
) ON [PRIMARY]

--insert sample data into item master table
INSERT INTO [itemMaster]
           ([ItemNumber]
           ,[ItemDescription])
     VALUES
           ('57893','ItemDescription1')
           ,('57894','ItemDescription2')
           ,('57895','ItemDescription3')
           ,('57896','ItemDescription4')
           ,('57897','ItemDescription5')

--create reference table
CREATE TABLE [dbo].[RefTable](
	[ReferenceNumber] [varchar](12) NOT NULL,
	[ItemNumber] [int] NOT NULL
) ON [PRIMARY]

--insert sample data into reference table
INSERT INTO [RefTable]
           ([ReferenceNumber]
           ,[ItemNumber])
     VALUES
           ('57893','PRE70')
           ,('57893','PRE71')
           ,('57894','PRE115')
           ,('57895','DIS50')
           ,('57896','39020043')
           ,('57897','39020057')


When I now run the following query as suggested:

SELECT 
 ItemNumber
,ItemDescription
,(SELECT ReferenceNumber FROM RefTable WHERE ItemNumber= m.ItemNumber FOR XML PATH('ItemReference'),type)
FROM itemMaster m
FOR xml path ('Item'),type , root ('AllParts')

I get a result that shows every reference number as its own element:

<AllParts>
  <Item>
    <ItemNumber>57893</ItemNumber>
    <ItemDescription>ItemDescription</ItemDescription>
    <ItemReference>
      <ReferenceNumber>PRE71</ReferenceNumber>
    </ItemReference>
    <ItemReference>
      <ReferenceNumber>PRE70</ReferenceNumber>
    </ItemReference>
  </Item>
...


but what I would like the XML to look like is as follows:

<AllParts>
  <Item>
    <ItemNumber>57893</ItemNumber>
    <ItemDescription>ItemDescription</ItemDescription>
    <ItemReference>
      <ReferenceNumber>PRE71</ReferenceNumber>
      <ReferenceNumber>PRE70</ReferenceNumber>
    </ItemReference>
  </Item>


How can I accomplish this?
Thanks for your patience and help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47062 Posts
Posted - 08/13/2012 :  14:28:51  Show Profile  Reply with Quote
here you go


SELECT 
 ItemNumber
,ItemDescription
,(SELECT ReferenceNumber FROM RefTable WHERE ItemNumber= m.ItemNumber FOR XML PATH(''),root('Itemreference'),type)
FROM itemMaster m
FOR xml path ('Item'),type , root ('AllParts')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000