How to display XML column data

Question

I have a table column consist with the XML files. I want to read XML data and display it.

I come up with the following code. But it read only one row in the column want to display other XML data also

declare @xml xml
select  @xml = event_data_XML from #temp

SELECT * FROM (
SELECT 
CAST(f.x.query('data(@name)') as varchar(150)) as data_name,
CAST(f.x.query('data(value)') as varchar(150)) as data_value
FROM @xml.nodes('/event') as t(n)
CROSS APPLY t.n.nodes('data') as f(x)) X
PIVOT (MAX(data_value) FOR data_name IN (NTDomainName, DatabaseName, ServerName)) as pvt

Output should be like this(NTDomainName, DatabaseName, ServerName are xml data)

We need to see some sample data here; we can't give an answer without there. All I can suggest is look into using `CROSS APPLY` on further nodes. — Thom A, Mar 02 '18 at 12:17
Possible duplicate of [How to query values from xml nodes?](https://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes) — Troy Turley, Mar 02 '18 at 12:51
Without knowing your XML (add a **consumable** sample - **no pictures!**) nobody can help you. Why are you reading from `#temp`. If there more than one row your `select @xml = event_data_XML from #temp` will fail... Please use the edit option to add some more details — Shnugo, Mar 02 '18 at 14:05

score 0 · Answer 1 · answered Mar 02 '18 at 14:12

There are a bunch of ways you could do this. I'll show you a way I think you'd find easiest.

To start, here's a table with a little test data:

CREATE TABLE dbo.stuff (
    id int identity (1,1) primary key
  , event_data_xml xml
  , create_date datetime default(getdate())
  , is_active bit default(1)
);

INSERT INTO dbo.stuff (event_data_xml)
VALUES ('<event name="thing" package="as">something</event>')
INSERT INTO dbo.stuff (event_data_xml)
VALUES ('<event name="otherthing" package="as">something else</event>')

---All records
SELECT * FROM dbo.[stuff];

Make sense so far? Here's the query I'd use if I wanted to mix XML data and column data:

---Parsed up
SELECT event_data_xml.value('/event[1]', 'nvarchar(max)') AS [parsed element #text]
     , event_data_xml.value('/event[1]/@name', 'nvarchar(max)') AS [parsed attribute value]
     , create_date --column from table
  FROM dbo.stuff
 WHERE is_active = 1;

Using the value() function on the XML column passing in an xpath to what I want to display and SQL Server data type for how I want it returned.

Just make sure you're selecting a single value with your xpath expression.

How to display XML column data

1 Answers1