tsql - xml question
-
it is possible make a query and the result will be:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="proyect" value="2901" />
<add key="user" value="usrsession" />
</appSettings>
</configuration>
i don't have a table or something, but, with a temporal table can do this?
____________________________________________________________________________
Rafo* -
This won't include the encoding header
DECLARE @t TABLE( VARCHAR(20), value VARCHAR(20))INSERT INTO @t(,value)
SELECT 'proyect','2901' UNION ALL
SELECT 'user','usrsession'
SELECT (
SELECT AS "@key",
value AS "@value"
FROM @t
FOR XML PATH('add'),ROOT('appSettings'),TYPE)
FOR XML PATH('configuration')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
To include the encoding, you'll need to change it to a varchar() data type. Otherwise SQL Server will internally use the encoding information but won't display it as part of the result set.
Based on Marks sample data it would look like:
SELECT '<?xml version="1.0" encoding="utf-8"?>'+CAST(
(SELECT (
SELECT AS "@key",
value AS "@value"
FROM @t
FOR XML PATH('add'),ROOT('appSettings'),TYPE)
FOR XML PATH('configuration')
)
AS VARCHAR(MAX))
-
Thanks,
that result can be export to a folder ? like "c:\config.xml"
it's possible?
____________________________________________________________________________
Rafo* -
Lutz - will doing that break the xml structure at all? I had a requirement to add the encoding header to an xml file and I did it just like you showed, converted the xml to a varchar and then concatenated the header. The developer I was working with said doing that made the xml invalid. I don't remember all the specifics, he ended up doing something on his end to add the encoding, but I've done it like that with no problems in the past. Curious if there's certain scenarios that can get you in trouble there.
xRafo - the import/export wizard in SSMS, bcp, or an SSIS package can all be used to export xml data to a local drive. Probably more options out there too.
-
xRafo (1/13/2012)
Thanks,that result can be export to a folder ? like "c:\config.xml"
it's possible?
There are several solutions:
a) SSMS (SQL Server Management Studio) , option "Results to File"
b) bcp (either started from a Windows console or using xp_cmdshell with all the security issue that would need to be taken care of...)
c) SSIS
It depends how often you'll need to do it, what technology you're comfortable with and what the security context allows you to do.
-
brendan woulfe (1/13/2012)
Lutz - will doing that break the xml structure at all? I had a requirement to add the encoding header to an xml file and I did it just like you showed, converted the xml to a varchar and then concatenated the header. The developer I was working with said doing that made the xml invalid. I don't remember all the specifics, he ended up doing something on his end to add the encoding, but I've done it like that with no problems in the past. Curious if there's certain scenarios that can get you in trouble there.xRafo - the import/export wizard in SSMS, bcp, or an SSIS package can all be used to export xml data to a local drive. Probably more options out there too.
As per my experience, it doesn't break the structure.
Two easy checks:
1) cast the varchar value back to xml worked just fine:
SELECT CAST('<?xml version="1.0" encoding="utf-8"?>'+CAST(
(SELECT (
SELECT AS "@key",
value AS "@value"
FROM @t
FOR XML PATH('add'),ROOT('appSettings'),TYPE)
FOR XML PATH('configuration')
)
AS VARCHAR(MAX)) AS XML)
2) saved the result to a txt file, renamed it to xml and it opened just fine.
I guess the trouble start when there's a encoding listed that either can't be converted from the target system or is invalid based on the content (e.g. invalid character for the encoding).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply