Working with XML in SQL Server
/Sometimes we need to work with XML inside of SQL. This guide will demonstrate some ways you might store and retrieve XML data from SQL.
For this first example, we are going to store the XML data in an nvarchar field and then cast it to xml when we query the data.
CREATE TABLE #FormData ( FormID int IDENTITY(1,1) ,FormDetailXML nvarchar(1024) ); INSERT INTO #FormData (FormDetailXML) VALUES ('<form><Organization>Contoso</Organization><Contact>Jake Jones</Contact><Title>Manager</Title><EmailFrom>JakeJones@contoso.com</EmailFrom><Phone>555-555-5555</Phone><Address>125 Cherry Ln</Address><City>NY</City><States>NY</States><Zip>99999-9999</Zip></form>'); INSERT INTO #FormData (FormDetailXML) VALUES ('<form><Organization>Northwind</Organization><Contact>Bill Blass</Contact><Title>Director</Title><EmailFrom>bblass@northwind.com.com</EmailFrom><Phone>333-333-3333</Phone><Address>1024 Bytes Blvd</Address><City>LA</City><States>CA</States><Zip>77777-7777</Zip></form>');
Now that we have our XML strings stored in the table let's cast the FormDetailXML to XML and view the results. Click on the XML link returned from the query, this will show you an organized view of the data.
SELECT FormID ,CAST(FormDetailXML AS xml) AS FormDetailXML FROM #FormData
Now we are going to SELECT from the table this time parsing the XML in to individual columns per XML element. XML will always have a Node, typically with Elements and/or Attributes. "Form" is the Node and inside the node are the different Elements. Organization, Title, Address, Phone, zip, etc., these are all the Elements we want to parse out in to individual columns.
Here is our query to parse our XML Elements to individual columns.
SELECT FormID ,CAST(FormDetailXML AS XML).value('data(/form/Organization)[1]','varchar(100)') AS Organization ,CAST(FormDetailXML AS XML).value('data(/form/Contact)[1]','varchar(100)') AS Contact ,CAST(FormDetailXML AS XML).value('data(/form/Title)[1]','varchar(100)') AS Title ,CAST(FormDetailXML AS XML).value('data(/form/EmailFrom)[1]','varchar(100)') AS EmailAddress ,CAST(FormDetailXML AS XML).value('data(/form/Phone)[1]','varchar(100)') AS PhoneNumber ,CAST(FormDetailXML AS XML).value('data(/form/Address)[1]','varchar(100)') AS HomeAddress ,CAST(FormDetailXML AS XML).value('data(/form/City)[1]','varchar(100)') AS CityCode ,CAST(FormDetailXML AS XML).value('data(/form/States)[1]','varchar(100)') AS StateCode ,CAST(FormDetailXML AS XML).value('data(/form/Zip)[1]','varchar(100)') AS Zipcode FROM #FormData
Let's breakdown a single Xquery line from the query above, we'll use the first line for Organization:
CAST(FormDetailXML AS XML).value('data(/form/Organization)[1]','varchar(100)') AS Organization
First we are casting the FormDetailXML data that is stored as nvarchar to XML:
CAST(FormDetailXML AS XML)
Next we use the value() method to perform an Xquery against our XML target, in this example the '(data(/form/Organization)[1]',
CAST(FormDetailXML AS XML).value
Ref: https://msdn.microsoft.com/en-us/library/ms178030(v=sql.105).aspx
Looking at the structure of our Xquery, we start with the word "data" followed by open paren'. "Data" is the data function and returns the typed value for each item specified.
('data(/form/Organization)[1]','varchar(100)')
Ref: https://msdn.microsoft.com/en-us/library/ms187038.aspx
Following the data function you see "(/form", that is our XML Node. This is followed by "/Organization", that is our XML Element. Finally we have our datatype, in this example varchar(100). This is the datatype you want to render the parsed Element to.*/
Note - There is another way you can write this query, moving the CAST function to a single line. Here is another way to query the XML data.
SELECT XMLFormDetails.value('data(/form/Organization)[1]','varchar(100)') AS Organization ,XMLFormDetails.value('data(/form/Contact)[1]','varchar(100)') AS Contact ,XMLFormDetails.value('data(/form/Title)[1]','varchar(100)') AS Title ,XMLFormDetails.value('data(/form/EmailFrom)[1]','varchar(100)') AS EmailAddress ,XMLFormDetails.value('data(/form/Phone)[1]','varchar(100)') AS PhoneNumber ,XMLFormDetails.value('data(/form/Address)[1]','varchar(100)') AS HomeAddress ,XMLFormDetails.value('data(/form/City)[1]','varchar(100)') AS CityCode ,XMLFormDetails.value('data(/form/States)[1]','varchar(100)') AS StateCode ,XMLFormDetails.value('data(/form/Zip)[1]','varchar(100)') AS Zipcode FROM (SELECT CAST(FormDetailXML AS xml) AS XMLFormDetails FROM #FormData) AS fdx
Drop our temp table.
DROP TABLE #FormData;
This should give you a basic understanding of how to get Element values from Nodes in an XML string stored in SQL, but what if you have XML that also has attributes and you want to return the Attribute value for a given Element.
If you have ever tried to parse data output from an Extended Event session you probably already have some scripts that help you accomplish this. Let's look at what we need to do to parse an XML string and retrieve Attributes for specific Elements. We will use the output from an Extended Events session in our next example.
CREATE TABLE #XESessionData ( FormID int IDENTITY(1,1) ,EventDetails nvarchar(1024) );
Below is data capture from an Event Session, we are going to insert this in to our table:
INSERT INTO #XESessionData (EventDetails) VALUES ('<event name="sql_statement_completed" package="sqlserver" timestamp="2015-02-13T20:56:19.482Z"><data name="duration"><value>23</value></data> <data name="cpu_time"><value>889745</value></data><data name="physical_reads"><value>1245</value></data> <data name="logical_reads"><value>22546</value></data><data name="writes"><value>17</value></data> <action name="collect_system_time" package="package0"><value>2015-02-13T20:56:19.482Z</value></action> <action name="session_id" package="sqlserver"><value>71</value></action><action name="database_name" package="sqlserver"><value>SQLAdmin</value></action> <action name="sql_text" package="sqlserver"><value>use [SQLAdmin]</value></action></event>');
Take a look at the data in its stored form and cast as XML. Click on the XML link to get a better view of the data.
SELECT EventDetails FROM #XESessionData; SELECT CAST(EventDetails AS xml) AS EventDetailsXML FROM #XESessionData; --Here is the query you'd use to parse the XML and get your specific values for certain attributes. SELECT xexml.value('(./action[@name="collect_system_time"]/value)[1]', 'varchar(4000)') as EventCollectTime ,xexml.value('(./action[@name="session_id"]/value)[1]', 'nvarchar(500)') as SessionID ,xexml.value('(./action[@name="database_name"]/value)[1]', 'nvarchar(500)') as DatabaseName ,xexml.value('(./data[@name="duration"]/value)[1]', 'bigint') as Duration ,xexml.value('(./data[@name="cpu_time"]/value)[1]', 'bigint') as CPUTime ,xexml.value('(./data[@name="physical_reads"]/value)[1]', 'bigint') as PhysicalReads ,xexml.value('(./data[@name="logical_reads"]/value)[1]', 'bigint') as LogicalReads ,xexml.value('(./data[@name="writes"]/value)[1]', 'bigint') as Writes ,xexml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(4000)') as SQLText FROM (SELECT CAST(EventDetails AS XML) XMLEventData FROM #XESessionData ) AS EventTable CROSS APPLY XMLEventData.nodes('/event') n (xexml)
In the example above, we wrote this query in the same format as the second query in our first example. Here we also used the nodes() method to target the parent node "/event". Our XML has a "data" attribute that contains a child element for the "value", it also has an "action" attribute with a child element as well.
Ref: https://msdn.microsoft.com/en-us/library/ms188282.aspx
In the example below we are calling to the specific value of "duration", we are looking for the value found in the "value" child element of the duration attribute.
xexml.value('(./data[@name="duration"]/value)[1]', 'bigint')
Working through the above queries should give you a solid start in working with XML in SQL.
DROP TABLE #XESessionData