Query Result to XML - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Result to XML

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-03-01 : 10:04:18

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 - 2012-03-01 : 10:47:25

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 11:27:21

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/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-25 : 17:23:21

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 19:42:00

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/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-29 : 13:16:52

would you mind pointing me into the right direction? How would I need to construct the subquery?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 14:35:33

[code]
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')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-08-13 : 14:22:02

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 14:28:51

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources