How to extract data from xml node in SQL

Question

Below is my xml, which is recorded in a database table.

<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
        </webpart>
    </pagelocation>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
      </pagelocation>
</root>

I need to get only the information belonging to <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d"> how to write a query for something like this in SQL

Gottfried Lesigang · Accepted Answer · 2016-11-10 10:40:09Z

I understand this as follows:

this XML (and many other XMLs) are living in a table's column in many rows
Each of these XMLs have more or less <webpart> nodes
Within one XML an @id is unique
You want to read the text within this given webpart element

The following code will insert three rows into a declared table simulating three different cases.

DECLARE @tbl TABLE(ID INT IDENTITY,Descritpion VARCHAR(100),XmlColumn XML);
INSERT INTO @tbl VALUES
('Contains the Id'
,N'<root>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Some Content 
            </webpart>
      </pagelocation>
</root>')
,('Does not contain the Id'
,N'<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Other Content 
        </webpart>
    </pagelocation>
</root>')
,('Multiple IDs, one of them fitting'
,N'<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            id is not correct
        </webpart>
    </pagelocation>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Same here 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Yeah! that is is 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                    One more 
            </webpart>
      </pagelocation>
</root>')

--The id you are searching for can be defined as parameter

DECLARE @SearchFor NVARCHAR(100)=N'editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d';

--This command uses .query() first to get hands on the right node, than .value to get the textual content

SELECT *
      ,XmlColumn.query('//webpart[@id=sql:variable("@SearchFor")]').value('.','nvarchar(max)') AS Content
FROM @tbl;

--This command uses the XQuery in .value() directly (faster, stops at the first occurance)

SELECT *
      ,XmlColumn.value('(//webpart[@id=sql:variable("@SearchFor")])[1]','nvarchar(max)') AS Content
FROM @tbl

It is OK to use CROSS APPLY .nodes() (like in other answers), but - if you a) don't expect more than one row, or b) want to read different values from one location, it is kind of overhead...

Giorgi Nakeuri · Accepted Answer · 2016-11-09 13:10:12Z

If I got you right(your "only" is not clear), here is an example:

DECLARE @xml XML = '
<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
        </webpart>
    </pagelocation>
    <pagelocation>
        <nodeid>3180</nodeid>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
      </pagelocation>
</root>'

SELECT n.c.value('@id', 'nvarchar(max)') FROM 
@xml.nodes('/root/pagelocation/webpart') AS n(c)

Output:

editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127
editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310
editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d
editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127

TT. · Accepted Answer · 2016-11-09 13:35:39Z

This uses an XPath expression to look up the node(s) with the specific ID. This uses the nodes() function in SQL Server, CROSS APPLY to apply it to the XML in the table, and selects using the value() function.

If you are unclear on how this works: research XPath expressions, XPath/XQuery functionality in SQL Server and CROSS APPLY.

DECLARE @t TABLE(x XML);
INSERT INTO @t(x)VALUES('<root>
  <pagelocation>
    <nodeid>3178</nodeid>
      <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
            Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
        </webpart>
    </pagelocation>
    <pagelocation>
        <NodeId>3180</NodeId>
            <webpart id="editabletexttitle;a36d4858-5d61-49b6-a860-221ad0b72310">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d">
                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
            <webpart id="editabletextdescriptio2;b5518b76-9fe6-47d2-8d8b-4ab169d3a127">
                        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse non nisl lacus. Donec in rutrum lorem, consectetur semper nunc. 
            </webpart>
      </pagelocation>
</root>');

SELECT
    n.v.value('.','NVARCHAR(256)')
FROM
    @t AS t
    CROSS APPLY t.x.nodes('//webpart[@id="editabletextdescriptio1;f4873da3-bf3b-43d3-9dc6-cdabfa8c7b6d"]') AS n(v);

Collectives™ on Stack Overflow

How to extract data from xml node in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related