Get data type from xml - SQL

Question

I want to extract the column name, value and data type from xml.

DECLARE @Xml XML = (Select Top 1 T.* From(VALUES('John',26,GETDATE())) As T(Name,Age,DateOfJoin)
                    FOR XML RAW, ELEMENTS);
Select
    ColumnName = T.X.value('local-name(.)', 'varchar(max)'),
    ColumnValue = T.X.value('text()[1]', 'nvarchar(128)'),
    DataType = '?'
From @Xml.nodes('/row/*') as T(X)

Output:

I can get the data type in xml, but couldn't parse.

DECLARE @Xml XML = (Select Top 1 T.* From(VALUES('John',26,GETDATE())) As T(Name,Age,DateOfJoin)
                    FOR XML RAW, ELEMENTS, XMLSCHEMA);
Select @Xml As XML

Output:

XML - Pretty Print:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" 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="row">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="Name">
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
                            <xsd:maxLength value="4" />
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
                <xsd:element name="Age" type="sqltypes:int" />
                <xsd:element name="DateOfJoin" type="sqltypes:datetime" />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"><Name>John</Name><Age>26</Age><DateOfJoin>2019-01-13T16:28:40.903</DateOfJoin></row>

You'd need to use dynamic SQL for this. Am I reading your xml right though, or does name really have a max length of 4? Your name alone shows that names far more than often have more than 4 characters. You could have some (big) truncation issues here. — Thom A
– Thom A ♦, Commented Jan 13, 2019 at 11:17
@Larnu, i just want to format the value based on data type, for ex: Case DataType When 'date' Then Format(Value,'dd-MMM-yyyy') When 'smalldatetime' Then Format(Value,'dd-MMM-yyyy HH:mm') Else Value End,....So that i need data type — Palanikumar
– Palanikumar, Commented Jan 13, 2019 at 11:19

John Cappelletti · Accepted Answer · 2019-01-13 22:58:49Z

1

There is another option here: sys.dm_exec_describe_first_result_set

Example

Declare @tsql nvarchar(max) =  'Select * From(VALUES(''John'',26,getdate())) As T(Name,Age,DateOfJoin)'

DECLARE @Xml XML = (Select * From(VALUES('John',26,getdate())) As T(Name,Age,DateOfJoin) FOR XML RAW, ELEMENTS);


Select ColumnName  = T.X.value('local-name(.)', 'varchar(max)'),
       ColumnValue = T.X.value('text()[1]', 'nvarchar(128)')
      ,DataType    = B.system_type_name
 From  @Xml.nodes('/row/*') as T(X)
 Join  sys.dm_exec_describe_first_result_set(@SQL,null,null)  B
   on  T.X.value('local-name(.)', 'varchar(max)') = B.name

Returns

ColumnName  ColumnValue             DataType
Name        John                    varchar(4)
Age         26                      int
DateOfJoin  2019-01-13T09:00:51.337 datetime

edited Jan 13, 2019 at 22:58

answered Jan 13, 2019 at 14:50

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Palanikumar Over a year ago

Thanks! It is working, But I have to write the query 2 times.

Palanikumar · Accepted Answer · 2019-01-16 10:32:24Z

From @Larnu's answer

DECLARE @Xml XML = (Select Top 1 T.* From(VALUES('John',26,GETDATE())) As T(Name,Age,DateOfJoin)
                    FOR XML RAW, ELEMENTS, XMLSCHEMA);


WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' AS xsd)
SELECT ColumnName = X.e.value('@name','sysname'),
       ColumnValue = T.X.value('text()[1]', 'nvarchar(max)'),
       DataType = REPLACE(ISNULL(X.e.value('@type','nvarchar(140)'), X.e.value('(./xsd:simpleType/xsd:restriction/@base)[1]','nvarchar(140)')), 'sqltypes:', '')
FROM @XML.nodes('xsd:schema/xsd:element/xsd:complexType/xsd:sequence/xsd:element') X(e)
INNER JOIN @Xml.nodes('/*/*') as T(X) ON T.X.value('local-name(.)', 'nvarchar(128)') = X.e.value('@name','sysname')

Output:

Collectives™ on Stack Overflow

Get data type from xml - SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related