3

I have below sql table. I need to add a column xmls to my table such that the table looks like below.

CREATE TABLE employees(
      id          integer,
      name        VARCHAR(MAX),
      designation VARCHAR(MAX),
      manager     integer,
      hired_on    date,
      salary      integer,
      commission  float,
      dept        integer);
INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);
INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3);
INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3);
INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3);
SELECT * FROM employees WHERE id < 10;

The new column xmls should look like below(last column below), I know I can do it with the help of string utilities but cannot figure the simple yet effective and optimal solution.

enter image description here

This is what I tried but this does not give me the end tag summary.

SELECT (
SELECT id AS '@id',
designation AS '@type',
salary AS '@salary'
FROM employees b 
WHERE b.id = a.id
FOR XML PATH('summary'))
FROM employees a

enter image description here

4
  • 2
    <summary /> and <summary></summary> are the same thing Jul 31, 2023 at 10:51
  • I obviously know that,its the parser on which i don't have no control. Jul 31, 2023 at 11:54
  • 1
    Which parser are you using that it actually makes a difference? No good parser should care. Jul 31, 2023 at 12:37
  • 3
    Stating the obvious: if the "xml parser" does not parse either form, it's not an XML parser. Jul 31, 2023 at 17:57

2 Answers 2

10

Strictly speaking, a self-closing tag <summary /> and a full tag <summary></summary> have the same meaning in XML, so it shouldn't make a difference to you, or to any proper XML parser.

But if you want to force it to generate a full tag as opposed to a self-closing one, you can add an empty text() column:

SELECT (
    SELECT
      e.id AS [@id],
      e.designation AS [@type],
      e.salary AS [@salary],
      '' AS [text()]
    FOR XML PATH('summary')
  )
FROM employees e;

db<>fiddle

You don't need to self-join in the subquery, you can refer to the outer columns directly. Note also that you should use [] to quote columns where necessary, not ''.


Side note: you don't need to store this XML, you can create a view that brings it up when you query it.

2
  • You don't need to self-join in the subquery, you can refer to the outer columns directly. how?? Jul 31, 2023 at 11:52
  • 1
    As shown, there is no self-join. In subqueries you are allowed to refer to outer tables Jul 31, 2023 at 12:32
-1

I would recommend not doing this at all.

All the data values in the XML are present in other fields within the record, which makes the XML a derived field.

General Rule: do not store Data that you can derive from other Data.

Instead, build the XML "on demand", in a Query, or in a View or in a calculated column.

At its simplest (albeit most likely to break if XML-meaningful characters aren't properly escaped):

select '<summary' 
  || ' id='\'' || id || '\''' 
  || ' type='\' || designation || '\''
  || ' salary='\' || salary || '\'' 
  || '\'/>' xml 
from table1 

And, as others have said, <summary></summary> and <summary/> are completely equivalent in XML. (To [X]HTML? Perhaps not, but to XML itself? Yes).

2
  • 4
    That's not valid T-SQL, and even in other systems that won't generate correct XML always (needs proper escaping). build the XML "on demand" that is what OP is doing already using FOR XML, it's just not working quite right. Jul 31, 2023 at 12:36
  • 1
    My point is that the OP is trying to create and then /store/ the XML content in a new column. That's a Bad Idea under any DBMS.
    – Phill W.
    Aug 1, 2023 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.