Menu
we have a simple table and need convert to XML
Declare @Person TABLE
( [BusinessEntityID] [int] NOT NULL,
[PersonType] [varchar](2) NULL,
[Title] [varchar](30) NULL,
[FirstName] [varchar](30) NULL,
[MiddleName] [varchar](30) NULL,
[LastName] [varchar](30) NULL)
Insert Into @Person Values(10001,'IN','Article','Carolyn','Jo','Alonso')
we need this xml for output with For XML in sql server
<?xml-stylesheet type='text/xsl' href='result.xsl'?>
<documents>
<document>
<field name="BusinessEntityID">10001</field>
<field name="PersonType">IN</field>
<field name="Title">Article</field>
<field name="FirstName">Carolyn</field>
<field name="MiddleName">Jo</field>
<field name="LastName">Alonso</field>
</document>
</documents>
348
XML values can be stored natively in an xml data type column that can be typed according to a collection of XML schemas, or left untyped. You can index the XML column. The ability to specify an XQuery query against XML data stored in columns and variables of the xml type.
Limitations of the xml data typeThe stored representation of xml data type instances can't exceed 2 GB. It can't be used as a subtype of a sql_variant instance. It doesn't support casting or converting to either text or ntext.
SQL Server provides an XML schema that defines syntax for writing XML format files to use for bulk importing data into a SQL Server table. XML format files must adhere to this schema, which is defined in the XML Schema Definition Language (XSDL).
You can transfer XML data into SQL Server in several ways. For example: If you have your data in an [n]text or image column in a SQL Server database, you can import the table by using Integration Services. Change the column type to XML by using the ALTER TABLE statement.
The following XML PATH query should get you most of the way. You just need to add the stylesheet header.
SELECT
'BusinessEntityID' AS 'document/field/@name'
, BusinessEntityID AS 'document/field'
, '' AS 'document'
, 'documentType' AS 'document/field/@name'
, PersonType AS 'document/field'
, '' AS 'document'
, 'Title' AS 'document/field/@name'
, Title AS 'document/field'
, '' AS 'document'
, 'FirstName' AS 'document/field/@name'
, FirstName AS 'document/field'
, '' AS 'document'
, 'MiddleName' AS 'document/field/@name'
, MiddleName AS 'document/field'
, '' AS 'document'
, 'LastName' AS 'document/field/@name'
, LastName AS 'document/field'
, '' AS 'document'
FROM @Person
FOR XML PATH(''), ROOT('documents')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
