How to Get SQL to Generate Both the Starting and Ending Xml Tags

Generating self-closing xml tags for lists in SQL Server

Casting '' as xml returns a self closing element or query('.') the generated xml, TYPE is required.

SELECT 
    Apple = '', 
    Banana = cast('' as xml), 
    Orange = '', 
    Pear = cast('' as xml)
FOR XML RAW ('Fruits'), ELEMENTS, TYPE;

SELECT 
(SELECT 
    Apple = '', 
    Banana = '', 
    Orange = '', 
    Pear = ''
FOR XML RAW ('Fruits'), ELEMENTS, TYPE).query('.');

Self-closing tags in XML in SQL Server

Actually If we inner the XML then it will return the Tag as self closing. Please see the below SQL code:

DECLARE @TempData Table
(
Column1 NVARCHAR(250)
)
INSERT INTO @TempData values('Column1')
INSERT INTO @TempData values('Column2')
INSERT INTO @TempData values('')
SELECT
(
SELECT * FROM @TempData FOR XML PATH('Test'), Type
)
For XML PATH (''),
ROOT('Rows')

Output:

<Rows>
  <Test>
    <Column1>Column1</Column1>
  </Test>
  <Test>
    <Column1>Column2</Column1>
  </Test>
  <Test>
    <Column1 />
  </Test>
</Rows>

SQL - XML | How to create sub tags on XML document using FOR XML

Please try the following solution.

I am not sure about the relationships between tables.

You may need to adjust the WHERE clauses...

SQL

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS [dbo].[Categories];
DROP TABLE IF EXISTS [dbo].[Equipment];
DROP TABLE IF EXISTS [dbo].[Materials];
DROP TABLE IF EXISTS [dbo].[Recipe];

CREATE TABLE [dbo].[Categories](
    [id] [int] NULL,
    [name] [nvarchar](150) NULL
);
CREATE TABLE [dbo].[Equipment](
    [id] [int] NULL,
    [equipment_name] [nvarchar](50) NULL,
    [category_id] [int] NULL
);
CREATE TABLE [dbo].[Materials](
    [id] [int] NULL,
    [material_name] [nvarchar](50) NULL
);
CREATE TABLE [dbo].[Recipe](
    [equipment_id] [int] NULL,
    [material_id] [int] NULL,
    [material_quantity] [int] NULL
);

INSERT [dbo].[Categories] ([id], [name]) VALUES 
(1, N'Warrior Weapons'),
(2, N'Hunter Weapons'),
(3, N'Mage Weapons'),
(4, N'Warrior Armours');

INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES 
(1, N'Claymore', 1),
(2, N'Bow', 2),
(3, N'Fire Staff', 3),
(4, N'Knight Armor', 4);

INSERT [dbo].[Materials] ([id], [material_name]) VALUES 
(1, N'Bar'),
(2, N'Leather'),
(3, N'Plank'),
(4, N'Cloth');

INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES 
(1, 1, 20),
(1, 2, 12),
(2, 3, 32),
(3, 3, 16),
(3, 1, 8),
(4, 1, 16);
-- DDL and sample data population, end

DECLARE @categoryID INT = 1;

SELECT @categoryID AS [@id]
, (
    SELECT * FROM dbo.Materials
    WHERE id <= 2
    FOR XML PATH('material'), TYPE, ROOT('materials')
)
, (
    SELECT * FROM dbo.Categories
    WHERE id = @categoryID
    FOR XML PATH('category'), TYPE
)
, (
    SELECT id, equipment_name, category_id AS category
    FROM dbo.Equipment
    WHERE category_id = @categoryID
    FOR XML PATH('equipment'), TYPE
)
, (
    SELECT *
    FROM dbo.Recipe
    WHERE equipment_id = 1 AND material_id <= 2
    FOR XML PATH('recipe'), TYPE, ROOT('recipes')
)
FOR XML PATH('Gear'), TYPE;

Output

<Gear id="1">
  <materials>
    <material>
      <id>1</id>
      <material_name>Bar</material_name>
    </material>
    <material>
      <id>2</id>
      <material_name>Leather</material_name>
    </material>
  </materials>
  <category>
    <id>1</id>
    <name>Warrior Weapons</name>
  </category>
  <equipment>
    <id>1</id>
    <equipment_name>Claymore</equipment_name>
    <category>1</category>
  </equipment>
  <recipes>
    <recipe>
      <equipment_id>1</equipment_id>
      <material_id>1</material_id>
      <material_quantity>20</material_quantity>
    </recipe>
    <recipe>
      <equipment_id>1</equipment_id>
      <material_id>2</material_id>
      <material_quantity>12</material_quantity>
    </recipe>
  </recipes>
</Gear>

How to break up two xml tags with the same subchild names in SQL

Just place something empty in between:

SELECT
 'blah' AS [RequestHeader/RequestID],
'Deal.Trial' AS [RequestHeader/Action],
'DoDealValidate' AS [RequestHeader/ActionFlags/Flag],
NULL AS [RequestHeader/ActionFlags],
'DoDealDerive' AS [RequestHeader/ActionFlags/Flag] 
FOR XML PATH('row');

The background:

The engine is running through the SELECT's columns and builds them one after the other.

• Well, there is a <RequestHeader> to open
• and there is a <RequestID> to open
• Again the <RequestHeader>, still open, nothin to to
• and there is <Action> below... Oh, we must close the <RequestID> and open a new <Action>
• and so on...

In your code the <Flag> is still open, therefore the content is written into the open element.

My change will let the engine think

• Ah, we move up one level, so we close the <Flag> first... Oops, there's nothing to write...
• Now there is something for <Flag>, which is not open anymore, we have to re-open a (new) <Flag> node
• and so on...

Generate single XML file from SQL Server with multiple results per customer

There are two major flaws:

• Never create an XML via string concatenation. Just imagine, one of your text columns contains data like 'Do & Co'. The & would break your XML's validity!
• Never use culture depending date formats! I did not know, whether your date values are the second of January or the first of February. Always use a secure and independant format such as ISO8601 (especially within XML)!

Try it like this:

There's a mockup scenario which you'll have to adapt to your needs. The basic idea is to use FOR XML PATH() for the billing data and another sub-select - again with FOR XML PATH(),TYPE for the related (nested) activities.

DECLARE @mockupBilling TABLE(StatementDate DATE, ID INT, Balance DECIMAL(10,4),Name VARCHAR(100),Msg VARCHAR(MAX));
INSERT INTO @mockupBilling VALUES(GETDATE(),987654,85.0,'Test Person','You have an outstanding balance.');

DECLARE @mockupActivity TABLE(BillingID INT, ActivityDate DATE, Activity VARCHAR(100), Charge DECIMAL(10,4));
INSERT INTO @mockupActivity VALUES
 (987654,{d'2018-01-02'},'WINTER17D Remaining Balance',50.0)
,(987654,{d'2018-01-03'},'SPRING18A Remaining Balance',15.0);

SELECT b.StatementDate AS [stmt_date]
      ,b.ID AS [id]
      ,b.Balance AS [total_due]
      ,b.Name AS [name]
      ,b.Msg AS [messge]
      ,(
        SELECT a.ActivityDate AS [activity_date]
              ,a.Activity AS [activity_desc]
              ,a.Charge AS [charge]
        FROM @mockupActivity AS a
        WHERE a.BillingID=b.ID
        ORDER BY a.ActivityDate
        FOR XML PATH('activity'),TYPE
       ) 
FROM @mockupBilling AS b
FOR XML PATH('Billing');

The result

<Billing>
  <stmt_date>2018-02-02</stmt_date>
  <id>987654</id>
  <total_due>85.0000</total_due>
  <name>Test Person</name>
  <messge>You have an outstanding balance.</messge>
  <activity>
    <activity_date>2018-01-02</activity_date>
    <activity_desc>WINTER17D Remaining Balance</activity_desc>
    <charge>50.0000</charge>
  </activity>
  <activity>
    <activity_date>2018-01-03</activity_date>
    <activity_desc>SPRING18A Remaining Balance</activity_desc>
    <charge>15.0000</charge>
  </activity>
</Billing>

How to generate XML with open and close tag for NULL/empty value columns in sql?

<empemail /> is the standard way to represent an empty field.

You can replace the value with a space or some other value:

select e.empid, e.empname,
       (select ed.empaddress, coalesce(ed.empemail, ' ') as empemail
         from empdetail ed
         where e.empid = ed.empid
         for xml path('EmployeeDetails'), type
        ) 
from emp e
where e.empid = 3
for xml path('root')

Here is a db<>fiddle.

Extracting xml tag and values with sql stored procedure

Your xml fragment is not a valid XML. Tag names cannot start with a number or punctuation character.

Now if you were having an xml fragment like this

DECLARE @xml XML =
'<root>
    <_3018>1</_3018>
    <_3017>2</_3017>
</root>'

Then you can get names and values with a query

SELECT
    CAST(REPLACE(N.value('local-name(.)', 'VARCHAR(12)'), '_', '') AS INT) tagname,
    N.value('(.)[1]', 'INT') tagvalue 
 FROM @xml.nodes('/root/*') as T(N)

Output:

| TAGNAME | TAGVALUE |
----------------------
|    3018 |        1 |
|    3017 |        2 |

Here is SQLFiddle demo

How to select values between a XML tag in SQL Query

Do not use regular expressions to parse XML; use a proper XML parser.

However, what you have is not properly formed XML as it is missing a root element and you are missing the / in all of the closing tags; so you first need to fix your XML and give it a root element and then you can parse it using an XML parser.

SELECT x.*
FROM   table_name t
       CROSS APPLY XMLTABLE(
         '//root'
         PASSING XMLTYPE( '<root>' || t.data || '</root>' )
         COLUMNS
           domain   VARCHAR2(10) PATH './ID/DOMAIN',
           domainid VARCHAR2(10) PATH './ID/DOMAINID',
           gp       VARCHAR2(50) PATH './GROUP/GP',
           rsn      VARCHAR2(50) PATH './GROUP/RSN'
       ) x

Which, for the sample data:

CREATE TABLE table_name ( data ) AS
SELECT '<ID>
  <DOMAIN>IND</DOMAIN>
  <DOMAINID>112AC</DOMAINID>
</ID>
<GROUP>
  <GP>ASIA</GP>
  <RSN>GOOD</RSN>
</GROUP>' FROM DUAL

Outputs:

DOMAIN | DOMAINID | GP   | RSN 
:----- | :------- | :--- | :---
IND    | 112AC    | ASIA | GOOD

If you just want a single value then you can use XMLQUERY:

SELECT  XMLQUERY(
          '/root/ID/DOMAINID/text()'
          PASSING XMLTYPE( '<root>'||data||'</root>' )
          RETURNING CONTENT
        ) AS domainid
FROM    table_name

Which outputs:

| DOMAINID |
| :------- |
| 112AC    |

db<>fiddle here

---

Update

I am going to assume that your XML also defines the xsi and busHdr namespaces (if it doesn't then Oracle will fail to parse the XML as it does not know what those namespaces are); that would give you this sample data:

CREATE TABLE table_name ( data ) AS
SELECT '<?xml version="1.0" encoding="US-ASCII"?>
<GML:GMMessage 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:GML="GML" 
   xmlns:busHdr="busHdr"
   xmlns:GMLType="GML.Type"
   
   xsi:schemaLocation="GML ../schema/gml.xsd busHdr ../schema/bushdr.xsd"
   SchemaVersion="9.8">
    <BusinessHdr>
       <busHdr:BusObjectType>ABC</busHdr:BusObjectType>
       <busHdr:BusObjectOwner>HDHDH</busHdr:BusObjectOwner>
       <busHdr:BusObjectId>DJHDAHDAJHDA</busHdr:BusObjectId>
       <busHdr:BusObjectVersion>1</busHdr:BusObjectVersion>
      </BusinessHdr>
    <Transaction>
     <GenericEvent>NEW</GenericEvent>
     <Group>
        <GroupId>3424234</GroupId>
        <Reason>MANUAL</Reason>
    </Group>
       <Id>
           <Domain>ssdsgdsg</Domain>
           <DomainId>123456ACC</DomainId>
           <Version>1</Version>
       </Id>
       <Date>2021-02-01</Date>
     </Transaction>
    </GML:GMMessage>' FROM DUAL

Then, you just need to add the namespace that you are using and update the paths to the new (case-sensitive) locations:

SELECT x.*
FROM   table_name t
       CROSS APPLY XMLTABLE(
         XMLNAMESPACES( 'GML' AS "GML" ),
         '//GML:GMMessage/Transaction'
         PASSING XMLTYPE( t.data )
         COLUMNS
           domain   VARCHAR2(10) PATH './Id/Domain',
           domainid VARCHAR2(10) PATH './Id/DomainId',
           version  NUMBER(3,0)  PATH './Id/Version',
           groupid  VARCHAR2(50) PATH './Group/GroupId',
           reason   VARCHAR2(50) PATH './Group/Reason',
           dt       DATE         PATH './Date'
       ) x

Outputs:

DOMAIN   | DOMAINID  | VERSION | GROUPID | REASON | DT       
:------- | :-------- | ------: | :------ | :----- | :--------
ssdsgdsg | 123456ACC |       1 | 3424234 | MANUAL | 01-FEB-21

db<>fiddle here

How to retrieve two different XML tags using FROM OPENXML in SQLServer

Try this - using the native XQuery support in SQL Server (2005 and newer):

DECLARE @Id_XML XML ='<OWNER>
      <NAME>Lucas</NAME>
      <EMAIL>lucas@sample.com</EMAIL>
    </OWNER>
    <ITENS>
      <MEMBER Nitem="1">
           <NAME>Alex</NAME>
           <EMAIL>alex@sample.com</EMAIL>
      </MEMBER>
      <MEMBER Nitem="2">
           <NAME>Jane</NAME>
           <EMAIL>jane@sample.com</EMAIL>
      </MEMBER>
    </ITENS>'

SELECT
    OwnerName = xc.value('(NAME)[1]', 'varchar(50)'),
    OwnerEmail = xc.value('(EMAIL)[1]', 'varchar(250)'),
    MemberItem = xc2.value('@Nitem', 'int'),
    MemberName = xc2.value('(NAME)[1]', 'varchar(50)'),
    MemberEMail = xc2.value('(EMAIL)[1]', 'varchar(250)')
FROM
    @Id_XML.nodes('/OWNER') AS XT(XC)
CROSS APPLY
    @Id_XML.nodes('/ITENS/MEMBER') AS XT2(XC2)

Gives an output of:

---

Related Topics