Dynamically Get XML data type Values in different Columns in SQL Server

Question

I have table with having one column with xml data type and contains values in xml format.

e.g.

<row><A>1</A><B>xyz</B></row>
<row><A>2</A><B>jkl</B></row>

And want output in table:

-------------
|  A  |  B  |
-------------
|  1  | xyz |
|  2  | jkl |
-------------

I want to show ouptup like this dynamically. Is there any function which returns xml data to table?

What do you mean you want to show it dynamically? What's dynamic about what you need? — Thom A
– Thom A ♦, Commented Jan 20, 2020 at 10:24
T-SQL hates dynamic result sets (in general; this is not restricted to XML). You can use the XML methods (.nodes, .query, .value) to get any part of the XML you please, but not in the form of dynamic columns (either in name or in type), while OPENXML wants a schema or else it returns a generic table. If you know your data looks exactly like this, you can write a query to turn it into a rowset. Otherwise, you either need to get ugly with dynamic SQL or just leave it to the client. — Jeroen Mostert
– Jeroen Mostert, Commented Jan 20, 2020 at 10:33
@Larnu - All columns should get dynamically without specifying columns name, typ — vinGa
– vinGa, Commented Jan 20, 2020 at 10:41
That isn't how SQL works, @vinGa . That's like expecting the put the command "MyTable" and expecting SQL Server to know that you want to SELECT all columns from it, along with any related rows which can be found by a foreign key. You need to tell SQL Server how you expect to see the results. — Thom A
– Thom A ♦, Commented Jan 20, 2020 at 10:43

Thom A · Accepted Answer · 2020-01-20 10:31:58Z

0

There appears to be nothing dynamic about what you want, and you just need to use XQUERY:

SELECT V.YourXML.value('(/row/A/text())[1]','int') AS A,
       V.YourXML.value('(/row/B/text())[1]','varchar(3)') AS B
FROM (VALUES(CONVERT(xml,'<row><A>1</A><B>xyz</B></row>')),
            (CONVERT(xml,'<row><A>2</A><B>jkl</B></row>')))V(YourXML);

answered Jan 20, 2020 at 10:31

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Comments

Suraj Kumar · Accepted Answer · 2020-01-20 12:01:08Z

0

You can check and try this query.

declare @X xml = '<row><A>1</A><B>xyz</B></row><row><A>2</A><B>jkl</B></row>';

select  x.r.value('(A)[1]', 'varchar(100)') as [A],
    x.r.value('(B)[1]', 'varchar(500)') as [B]
from    @X.nodes('/row') as x(r);

Here is the output:

A   B
-------
1   xyz
2   jkl

You can find the demo here. To access the node name and their values dynamically you can try the following query.

DECLARE @input XML =  '<row><A>1</A><B>xyz</B></row><row><A>2</A><B>jkl</B></row>'

SELECT
    NodeName = C.value('local-name(.)', 'varchar(50)'),
    NodeValue = C.value('(.)[1]', 'varchar(50)') 
FROM @input.nodes('/row/*') AS T(C)

It will give output as shown below. To know more you can refer this SO answer.

NodeName    NodeValue
--------------------
A           1
B           xyz
A           2
B           jkl

edited Jan 20, 2020 at 12:01

answered Jan 20, 2020 at 10:31

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

2 Comments

vinGa Over a year ago

Here you are giving column names,type. I don't want to give it, because actual table has more columns

Suraj Kumar Over a year ago

@vinGa You need to either provide the name or the index of the node to access the name values.

Collectives™ on Stack Overflow

Dynamically Get XML data type Values in different Columns in SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related