Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL to XML Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:06 PM
Points: 3, Visits: 12
Any suggestion on how to create XML like this from SQL Server?

I was able to create the XML without the Metadata lines using, "FOR XML RAW ('Request'), ELEMENTS".

<Request>
<RequestType>NEW</RequestType>
<CompanyName>Main St. Bistro</CompanyName>
<CompanyID>1234567890</MerchantID>
<Country>US</Country>
<PostalCode>60602</PostalCode>
<Metadata name="PROGRAM" global="true">A</Metadata>
<Metadata name="LEVEL" global="true">2</Metadata>
<Metadata name=”PROMOTION”>HAPPYMEAL</Metadata>
</Request>
Post #1447581
Posted Monday, April 29, 2013 9:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Bill.Smith (4/29/2013)
Any suggestion on how to create XML like this from SQL Server?

I was able to create the XML without the Metadata lines using, "FOR XML RAW ('Request'), ELEMENTS".

<Request>
<RequestType>NEW</RequestType>
<CompanyName>Main St. Bistro</CompanyName>
<CompanyID>1234567890</MerchantID>
<Country>US</Country>
<PostalCode>60602</PostalCode>
<Metadata name="PROGRAM" global="true">A</Metadata>
<Metadata name="LEVEL" global="true">2</Metadata>
<Metadata name=”PROMOTION”>HAPPYMEAL</Metadata>
</Request>


Perhaps the examples in this post will help give you some ideas:

SQL to XML Examples

 
Post #1447602
Posted Monday, April 29, 2013 9:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:30 AM
Points: 2,128, Visits: 5,565
Can you post also a small script that creates the table and insert test data? Without it I don't think that anyone will be able to help you with the SQL statement that converts the relational data to XML.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1447614
Posted Monday, April 29, 2013 9:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 35,770, Visits: 32,440
Heh... by the same token (pun intended), considering the tag bloat of XML, I'm still fasciated that anyone would actually use it especially for such flat data.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1447619
Posted Monday, April 29, 2013 10:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:06 PM
Points: 3, Visits: 12
-- Here's the sample data. I was able to get one Metadata line. I was hoping this would be easy to do using the XML functionality of SQL server. I was intending to put this in a stored procedure that returns XML. The stored procedure is consumed by an application that would write the XML to a file, etc... If this gets too complicated in T-SQL then the XML formatting could be moved off to the application.

declare @Request table
(
[RequestType] varchar(10)
,[CompanyName] varchar(50)
,[CompanyID] int
,[Country] varchar(2)
,[PostalCode] varchar(9)
--
,[Program] char(1)
,[Level] int
,[Promotion] varchar(10)
);

insert into @Request values ('NEW', 'Main St. Bistro', 1234567890, 'US', '60602', 'A', 2, 'HAPPYMEAL');

DECLARE @ExportXML XML

SET @ExportXML =
(
SELECT
[RequestType]
,[CompanyName]
,[CompanyID]
,[Country]
,[PostalCode]
--<Metadata name="PROGRAM" global="true">A</Metadata>
,'PROGRAM' 'Metadata/@name'
,'true' 'Metadata/@global'
,Program Metadata
-- Need the Level and Promotion as additonal Metada rows like this...
--<Metadata name="LEVEL" global="true">2</Metadata>
--,'PROGRAM' 'Metadata/@name'
--,'true' 'Metadata/@global'
--,Level Metadata
--<Metadata name=”PROMOTION”>HAPPYMEAL</Metadata>
FROM
@Request
FOR XML PATH(''), ROOT('Request')
)

SELECT @ExportXML AS ExportXML

--Trying to add additional Metadata rows results in
--Msg 6852, Level 16, State 1, Line 18
--Attribute-centric column 'Metadata/@name' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.
Post #1447644
Posted Monday, April 29, 2013 11:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:30 AM
Points: 2,128, Visits: 5,565
If I understood what you want, then you can use sub queries that create XML. Notice that when you do it you have to use the directive type (If you'll play with it a bit, you'll see why). Bellow is code that does it:
declare @Request table 
(
[RequestType] varchar(10)
,[CompanyName] varchar(50)
,[CompanyID] int
,[Country] varchar(2)
,[PostalCode] varchar(9)
--
,[Program] char(1)
,[Level] int
,[Promotion] varchar(10)
);

insert into @Request values ('NEW', 'Main St. Bistro', 1234567890, 'US', '60602', 'A', 2, 'HAPPYMEAL');

DECLARE @ExportXML XML

SET @ExportXML =
(
SELECT
[RequestType]
,[CompanyName]
,[CompanyID]
,[Country]
,[PostalCode]
--<Metadata name="PROGRAM" global="true">A</Metadata>
,'PROGRAM' 'Metadata/@name'
,'true' 'Metadata/@global'
,Program Metadata
-- Need the Level and Promotion as additonal Metada rows like this...
--<Metadata name="LEVEL" global="true">2</Metadata>
,(select 'LEVEL' as 'Metadata/@name'
,'true' 'Metadata/@global' for xml path (''), type)
--<Metadata name=”PROMOTION”>HAPPYMEAL</Metadata>
,(select 'PROMOTION' as 'Metadata/@name' for xml path(''), type)
FROM
@Request
FOR XML PATH(''), ROOT('Request')
)

select @ExportXML


Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1447663
Posted Monday, April 29, 2013 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:06 PM
Points: 3, Visits: 12
Adi. Thanks. That's exactly what I was looking for.
Post #1447665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse