CODEWITHSUNDEEP
sqlsql-serverxml
perplexedDev
perplexedDev

Reputation: 835

Constructing a XML in SQL with a table column name as XML tag

I have a requirement to get the schema of a table in XML format.

Consider a table Person with columns:

ID varchar(10) Identity, Name varchar(20), Designation varchar(10)

I need the XML to be of the format

<Persons>
<Person LocalizationSetting = {today's date}>
<ID type=varchar length =10 required=true/>
<Nametype=varchar length =20 required=true/>
<Designation  type=varchar length =10 required=true/>
</Person>
</Persons>

I have the following script

 DECLARE @TableSchema XML

SELECT @TableSchema = (
    select  column_name,
            data_type as [type],
            CHARACTER_MAXIMUM_LENGTH AS maxLength,
            case(is_nullable)
                when 'YES' then 'false'
                else 'true'    
            end as [required]
    from information_schema.columns [column]
    where table_name = 'Person' 
    for xml auto,root('Persons') 
    )

SELECT @TableSchema

I get the following result:

<Patients>
  <column column_name="ID" type="varchar"  maxLength="10" required="true" />
  <column column_name="Name" type="varchar" maxLength="20" required="true" />
  <column column_name="Designation" type="varchar" maxLength="10" required="true" />
</Patients>

Is it possible to have the column_name as a tag as shown in the expected result?

Upvotes: 0

Views: 909

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

You should solve this ...

I have a requirement to get the schema of a table in XML format.

... with standard built-in features. The schema you provide as expected output is some self-defined structure probably... Check this out:

CREATE TABLE Person  (ID VARCHAR(10) NOT NULL
                     ,NameType VARCHAR(20) NOT NULL
                     ,Designation VARCHAR(10) NOT NULL);
INSERT INTO Person VALUES('SomeID','SomeType','SomeDes');

SELECT * FROM Person FOR XML AUTO,XMLDATA;

--XMLDATA returns this schema

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="Person" content="empty" model="closed">
    <AttributeType name="ID" dt:type="string" />
    <AttributeType name="NameType" dt:type="string" />
    <AttributeType name="Designation" dt:type="string" />
    <attribute type="ID" />
    <attribute type="NameType" />
    <attribute type="Designation" />
  </ElementType>
</Schema>

--The next possibility

SELECT * FROM Person FOR XML AUTO,XMLSCHEMA;

--XMLSCHEMA returns this schema

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Person">
    <xsd:complexType>
      <xsd:attribute name="ID" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="10" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="NameType" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="20" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="Designation" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="10" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

If you really have to fullfill the expected format you are having the problem, that native queries do not support dynamic column names. As a workaround you might use dynamically created SQL to be run with EXEC or this approach, which is using a very ugly hack on string level (something one rather should avoid with XML!) at the end:

WITH AlmostCorrect AS
(
SELECT
    (
    SELECT  GETDATE() AS [@LocalizationSetting]
           ,(
            SELECT   COLUMN_NAME AS [@name]
                    ,DATA_TYPE AS [@type]
                    ,CHARACTER_MAXIMUM_LENGTH AS [@length]
                    ,CASE IS_NULLABLE
                        WHEN 'YES' THEN 'false'
                        ELSE 'true'    
                     END AS [@required]
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'Person' 
            FOR XML PATH('row'),TYPE
            ) AS [*]
    FOR XML PATH('Person'),ROOT('Persons'),TYPE
    ) AS TheXML
)
SELECT CAST(REPLACE(REPLACE(CAST(TheXML AS NVARCHAR(MAX)),'<row name="','<'),'" type',' type') AS XML)
FROM AlmostCorrect

The result

<Persons>
  <Person LocalizationSetting="2017-04-18T11:05:30.047">
    <ID type="varchar" length="10" required="true" />
    <NameType type="varchar" length="20" required="true" />
    <Designation type="varchar" length="10" required="true" />
  </Person>
</Persons>

Upvotes: 1

Related Questions