How do I add an XML schema? - 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

General SQL Server Forums

New to SQL Server Programming

How do I add an XML schema?

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-14 : 15:18:13

Hi,
How do I add an XML schema to my output?

I have this code which creates my schema as well as the desired data however I don't know how to create so that the output from the XML schema and the XML data comes in the same column.

Also, another question: Can I create an XML file (with this data) directly from MSSQL with T-SQL?


DECLARE @schema xml
DECLARE @data xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('HR.Leverantör'))

SET @data = (
SELECT
(
SELECT 
	RTRIM(orgnr) as 'ORGNR',
	RTRIM(namn) as 'NAMN',
	RTRIM(adress) as 'ADRESS',
	RTRIM(postnr) as 'POSTNR',
	RTRIM(ort) as 'ORT',
	RTRIM(telefon) as 'TELEFON',
	RTRIM(postgiro) as 'POSTGIRO'
 FROM HR.Leverantör
 FOR XML PATH ('RAD'), TYPE
)
FOR XML PATH('LEVERANTÖRER'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 23:59:35

you mean return schema and xml data as two column?
For second part, you can use bcp to export it to a file

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

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-15 : 16:54:59

Hi Visakh,
No I mean how do I return the schema and the XML data in the same column so I can export it to a file.

I will check out BCP, thanks.

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-15 : 17:09:32

For example, this creates the schema together with the data however now it is not XML:

USE IV132_XML

DECLARE @schema xml
DECLARE @data xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('HR.Leverantör'))

SELECT (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('HR.Leverantör')) + (SELECT
(
SELECT 
	RTRIM(orgnr) as 'ORGNR',
	RTRIM(namn) as 'NAMN',
	RTRIM(adress) as 'ADRESS',
	RTRIM(postnr) as 'POSTNR',
	RTRIM(ort) as 'ORT',
	RTRIM(telefon) as 'TELEFON',
	RTRIM(postgiro) as 'POSTGIRO'
 FROM HR.Leverantör
 FOR XML PATH ('RAD'), TYPE
)
FOR XML PATH('LEVERANTÖRER'), ELEMENTS --,XMLSCHEMA('HR.Leverantör')
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 01:03:45

i didn't really get purpose of doing this. whats the use of wrapping them inside same column?

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

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-16 : 21:56:07

I was confused, I have read a little bit more and now it looks good ...
... however now I have another question.

This is what I am using right now to create my XML document:

EXEC xp_cmdshell 'bcp "SELECT RTRIM(orgnr) as ''ORGNR'',RTRIM(namn) as ''NAMN'',RTRIM(adress) as ''ADRESS'',RTRIM(postnr) as ''POSTNR'',RTRIM(ort) as ''ORT'',RTRIM(telefon) as ''TELEFON'',RTRIM(postgiro) as ''POSTGIRO'' FROM IV132_XML.HR.Leverantör as LEVERANTORTABLE FOR XML AUTO, ELEMENTS, ROOT(''LEVERANTORROOT''),XMLSCHEMA(''LEVERANTORSCHEMA'')" queryout c:\Currency.xml -x -w -T'

If I run the SQL statement separately it works fine however with BCP it creates a new line:

</xsd:restriction></x
sd:simpleType>

If I manually removes the new line the XML document looks perfect.

Why does it create this new line?

Thanks in advance!

Best regards,
Tomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources