Querying XML Data in SQL Server

Question

In one of my table column (nvarchar), the data was stored in XML format.

In this example, the data looks like this :

<Datatype id="76" name="Disp_Dest" hdl="47/4/SB8_3-910-8243-19/0/76" odobjid="385">
    <Datatypevalue id="1" name="LowPressureRinse" />
    <Datatypevalue id="0" name="ShortInlet" />
</Datatype>

I checked a few SQL Server function, I need to define namespace in order to extract values out.

Formatted this properly so I can know which field to retrieve.

<Datatype id="76" name="Disp_Dest" hdl="47/4/SB8_3-910-8243-19/0/76" odobjid="385">
    <Datatypevalue id="1" name="LowPressureRinse" />
    <Datatypevalue id="0" name="ShortInlet" />
</Datatype>

The expected output would be

Disp_Dest, LowPressureRinse
Disp_Dest, ShortInlet

Do you think this is achievable using SQL Server XML related functions ?

Thanks.

Pரதீப் · Accepted Answer · 2017-08-18 05:12:51Z

1

Here is one way to do it using Value and Query method

DECLARE @xml XML = '<Datatype id="76" name="Disp_Dest" hdl="47/4/SB8_3-910-8243-19/0/76" odobjid="385">
        <Datatypevalue id="1" name="LowPressureRinse" />
        <Datatypevalue id="0" name="ShortInlet" />
</Datatype>'

SELECT CONVERT(VARCHAR(1000), @xml.query('data(Datatype/@name[1])')),
       c.value('@name', 'varchar(1000)')
FROM   @xml.nodes('Datatype/Datatypevalue') x (c)

answered Aug 18, 2017 at 5:12

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

1 Comment

user3015739 Over a year ago

It works when I tested your script in SQL Query but after converting it to a stored procedure, it didn't show up.

ALTER PROCEDURE [dbo].[usp_RetrieveDataType] @Param nvarchar(50) AS BEGIN DECLARE  @xml XML   	SET NOCOUNT ON;  SELECT @xml = CAST(convert(varchar(max),ExtendedData,1) as XML) FROM DataType WHERE Name =  @Param  SELECT CONVERT(VARCHAR(1000), @xml.query('data(Datatype/@name[1])')), c.value('@name', 'varchar(1000)') FROM @xml.nodes('Datatype/Datatypevalue') x (c)  END

Gottfried Lesigang · Accepted Answer · 2017-08-18 08:33:22Z

0

You should not store XML as a string. If you can change this, you should really use a natively typed XML column!

Why: The XML is not stored as the string you see, but as a hierarchy tree. Dealing with this tree does not need string parsing. But in your case you'll have to re-create this hierarchy tree in each call. This is very expensive!

Try it like this:

DECLARE @mockup TABLE(ID INT IDENTITY,YourStringXML XML);
INSERT INTO @mockup VALUES
(N'<Datatype id="76" name="Disp_Dest" hdl="47/4/SB8_3-910-8243-19/0/76" odobjid="385">
    <Datatypevalue id="1" name="LowPressureRinse" />
    <Datatypevalue id="0" name="ShortInlet" />
</Datatype>')
,(N'<Datatype id="11" name="SomeOther" hdl="blah" odobjid="111">
    <Datatypevalue id="1" name="blubb" />
    <Datatypevalue id="0" name="foo" />
</Datatype>');

SELECT m.ID
      ,A.Casted.value(N'/Datatype[1]/@id',N'int') AS dt_Id
      ,A.Casted.value(N'/Datatype[1]/@name',N'nvarchar(max)') AS dt_name
      ,A.Casted.value(N'/Datatype[1]/@hdl',N'nvarchar(max)') AS dt_hdl
      ,A.Casted.value(N'/Datatype[1]/@odobjid',N'nvarchar(max)') AS dt_odobjid
      ,dtv.value(N'@id',N'int') AS dtv_Id
      ,dtv.value(N'@name',N'nvarchar(max)') AS dtv_name
FROM @mockup AS m
OUTER APPLY (SELECT CAST(m.YourStringXML AS XML)) AS A(Casted)
OUTER APPLY A.Casted.nodes(N'/Datatype/Datatypevalue') AS B(dtv);

Just change @mockup to your actual table's name and adjust the column names.

The first APPLY will get the XML typed, the second APPLY uses .nodes() to get the internal <Datatypevalue> elements.

The result

+----+-------+-----------+-----------------------------+------------+--------+------------------+
| ID | dt_Id | dt_name   | dt_hdl                      | dt_odobjid | dtv_Id | dtv_name         |
+----+-------+-----------+-----------------------------+------------+--------+------------------+
| 1  | 76    | Disp_Dest | 47/4/SB8_3-910-8243-19/0/76 | 385        | 1      | LowPressureRinse |
+----+-------+-----------+-----------------------------+------------+--------+------------------+
| 1  | 76    | Disp_Dest | 47/4/SB8_3-910-8243-19/0/76 | 385        | 0      | ShortInlet       |
+----+-------+-----------+-----------------------------+------------+--------+------------------+
| 2  | 11    | SomeOther | blah                        | 111        | 1      | blubb            |
+----+-------+-----------+-----------------------------+------------+--------+------------------+
| 2  | 11    | SomeOther | blah                        | 111        | 0      | foo              |
+----+-------+-----------+-----------------------------+------------+--------+------------------+

answered Aug 18, 2017 at 8:33

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

4 Comments

user3015739 Over a year ago

Thanks for pointing out my error. I am not aware that I converted the XML to varchar. Will study your approach, hope I can understand it.

Gottfried Lesigang Over a year ago

@user3015739 Just ask if there are issues :-D

user3015739 Over a year ago

I created a store procedure and do the followings :

DECLARE @mockup TABLE(ID INT,YourStringXML XML);  	INSERT INTO @mockup ( ID, YourStringXML ) 	SELECT ID, CAST(CAST(ExtendedData AS VARCHAR(MAX)) AS XML) FROM DataType

However, the results after applying the query is NULL. Not sure what is missing

Gottfried Lesigang Over a year ago

Sry "mir"="not" :-D

Collectives™ on Stack Overflow

Querying XML Data in SQL Server

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related