CODEWITHSUNDEEP
sql-serverxmlt-sqlshred
Mar1009
Mar1009

Reputation: 811

Parse XML values

I'm new to XML parsing world and i'm need to extract a specific value from a XML field

This is my xml structure :

CREATE TABLE [dbo].[tempb](
    [RECID] [nvarchar](255) NOT NULL,
    [XMLRECORD] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[tempb] ([RECID], [XMLRECORD]) VALUES (N'100002','<row xml:space="preserve" id="100002">
  <c1>NEWT880101</c1>
  <c2>NEWMAN </c2>
  <c3>FIRST</c3>
  <c4>200</c4>
  <c179 />
  <c179 m="2">T</c179>
  <c179 m="3">MAINLAND</c179>
  <c179 m="4">AG</c179>
  <c179 m="5">AG</c179>
  <c179 m="6">343</c179>
  <c179 m="7">2124</c179>
  <c179 m="182" />
  </row>
  ')

INSERT [dbo].[tempb] ([RECID], [XMLRECORD]) VALUES (N'100003','<row xml:space="preserve" id="100002">
  <c1>NEWT880101</c1>
  <c2>NEWMAN </c2>
  <c3>FIRST</c3>
  <c4>2000</c4>
  <c179 />
  <c179 m="2">T</c179>
  <c179 m="3">MAINLAND</c179>
  <c179 m="4">AG</c179>
  <c179 m="5">AG</c179>
  <c179 m="6">343</c179>
  <c179 m="7">2124</c179>
  <c179 m="182" />
  </row>
  ')

I need to extract the C4 as new column along with RECID

Expected result:

RECID c4

100002 200

100003 2000

Upvotes: 0

Views: 129

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

As @AlwaysLearning alluded, it is a very simple case.

I edited your XML for clarity. Overall, what you need to do is called XML shredding. Here is how to do it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (RECID INT PRIMARY KEY, XMLRECORD XML);
INSERT INTO @tbl (RECID, XMLRECORD) VALUES
( 100002, N'<row xml:space="preserve" id="100002">
        <c1>NEWT880101</c1>
        <c2>NEWMAN</c2>
        <c3>FIRST</c3>
        <c4>200</c4>
    </row>')
, (100003, N'<row xml:space="preserve" id="100002">
        <c1>NEWT880101</c1>
        <c2>NEWMAN</c2>
        <c3>FIRST</c3>
        <c4>2000</c4>
    </row>');
-- DDL and sample data population, end

SELECT RECID
    , col.value('(c4/text())[1]', 'VARCHAR(20)') AS c4
FROM @tbl AS tbl
    CROSS APPLY tbl.XMLRECORD.nodes('/row') AS tab(col);

Output

+--------+------+
| RECID  |  c4  |
+--------+------+
| 100002 |  200 |
| 100003 | 2000 |
+--------+------+

Upvotes: 2

Related Questions