Query Nvarchar column containing XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Nvarchar column containing XML

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-07-24 : 14:20:27

Hi...
I've inherited a database that has a table with a column defined as nvarchar(max) datatype, but it contain XML data.

I need to query the xml data in the nvarchar column and pull out specific bits and also have a WHERE clause that can look for specific items.

The table, ProcesTracking, DDL (partial, but enough to explain) is:


  Create Table ProcessTracking
    (
     ProcessTrackingID  int identity (100,1) not null
     , ProcessDTim datetime
     , SourceSystem  nvarchar(50)
     , ProcessData nvarchar(max)    
    )

The ProcessData column is the one that contains the XML that is input from another system. Two examples would be like:


  <Process  Status="Sucess" Action="DataTransfer">
     <Value>
     Data transfer from Net23 successful
     </Value>
  </Proces>
  <Process  Status="Error" Action="DataTransfer">
     <Value>
     Unable to authenticate source system.
     </Value>
  </Proces>

I am not sure how to select out "Value" text and even how to search for a specific set...just the errors. ("Status = Error")

I think that I first have to convert the nvarchar to XML, is that right? I have that part handled (using a cte), but, I am not sure after that how to form the query using sql/xml.

If there are some good examples that folks know of, that would be helpful too.

thanks
- will

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:39:24

see illustration below


Create Table #ProcessTracking
    (
     ProcessTrackingID  int identity (100,1) not null
     , ProcessDTim datetime
     , SourceSystem  nvarchar(50)
     , ProcessData nvarchar(max)    
    )
	GO
	--some sample data to illustrate scenario
	INSERT #ProcessTracking (ProcessDTim,SourceSystem,ProcessData)
	VALUES (GETDATE()-18879,'System1','<Process  Status="Sucess" Action="DataTransfer">
     <Value>
     Data transfer from Net23 successful
     </Value>
  </Process>'),
  (GETDATE()-9187,'System2','<Process  Status="Error" Action="DataTransfer">
     <Value>
     Unable to authenticate source system.
     </Value>
  </Process>') 
  

  -- check inserted data
  select * from #ProcessTracking


  --now the solution 

  SELECT t.ProcessDTim,
  t.SourceSystem,
  m.n.value('./Value[1]','varchar(1000)') AS Val,
  m.n.value('./@Status','varchar(30)') AS Status
  FROM (SELECT ProcessDTim,SourceSystem,CAST(ProcessData AS xml) AS ProcessData FROM #ProcessTracking)t
  CROSS APPLY t.ProcessData.nodes('/Process')m(n)


  --filter on error node records
   SELECT t.ProcessDTim,
  t.SourceSystem,
  m.n.value('./Value[1]','varchar(1000)') AS Val,
  m.n.value('./@Status','varchar(30)') AS Status
  FROM (SELECT ProcessDTim,SourceSystem,CAST(ProcessData AS xml) AS ProcessData FROM #ProcessTracking)t
  CROSS APPLY t.ProcessData.nodes('/Process[./@Status="Error"]')m(n)
  

  --Drop table after test
  DROP TABLE #ProcessTracking

output
------------------------------------------------------------------
ProcessTrackingID	ProcessDTim	SourceSystem	ProcessData
100	1960-11-15 13:35:04.890	System1	<Process  Status="Sucess" Action="DataTransfer">
     <Value>
     Data transfer from Net23 successful
     </Value>
  </Process>
101	1987-05-30 13:35:04.890	System2	<Process  Status="Error" Action="DataTransfer">
     <Value>
     Unable to authenticate source system.
     </Value>
  </Process>




ProcessDTim	                        SourceSystem	                 Val	                                         Status
-------------------------------------------------------------------------------------------------------------------------------------------
1960-11-15 13:35:04.890	               System1	                          Data transfer from Net23 successful               Sucess
1987-05-30 13:35:04.890	               System2	                          Unable to authenticate source system.             Error



ProcessDTim	         SourceSystem	Val	                                            Status
----------------------------------------------------------------------------------------------------------
1987-05-30 13:35:04.890	System2	         Unable to authenticate source system.                Error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-07-24 : 16:09:48

quote:
Originally posted by visakh16
SQL Server MVP
http://visakhm.blogspot.com/

I don't swear very often...but Damn! Brilliant. Thanks so much for the help. I am going to be a follower of your blog now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 16:18:21

quote:
Originally posted by dhw

quote:
Originally posted by visakh16
SQL Server MVP
http://visakhm.blogspot.com/

I don't swear very often...but Damn! Brilliant. Thanks so much for the help. I am going to be a follower of your blog now.

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-08-03 : 14:20:10

quote:
Originally posted by visakh16
You're welcome

Hi, another question in regards to parsing out some XML data.

There is another set of xml data stored in a table that I don't seem to be able to use query as xml (like the above set of data).

Here is one example:


  <Results>[Device=Server23-SQL Status=Abort Error=PK Violation ExceptionCode=14509 RowID=7d90e612-01fc-82e3-ad42-c3599b87d43b]
  </Results>

What I have to do is get the stuff between the brackets [] in the Results and it would be the values after the = for each one.

I have tried to use the code that was provided and tweak it to use @Device or @Status, something like:


  SELECT t.ProcessDTim,
  t.SourceSystem,
  m.n.value('./Results[1]','varchar(1000)') AS XML_Val,
  m.n.value('./Results[1]@Device','varchar(30)') AS Device,
  m.n.value('./Results[1]@RowID,'varchar(30)') AS RowIDVal

  FROM (SELECT ProcessDTim,SourceSystem,CAST(ProcessData AS xml) AS ProcessData FROM #ProcessTracking)t
  CROSS APPLY t.ProcessData.nodes('/Process')m(n)

...but these simply return null for the Device, RowID or whatever element I use.

I am at the point with this one, to simply parse the data using Patindex and searching for the specific elements. But, i wanted to see if anyone had any thoughts regarding the XML way.

thanks
- will

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 15:23:11

all data required are inside value part of xml so you might have to first get value and then parse fields outusing pattern indexes rather than trying to get them as attributes

like


SELECT *,
CASE WHEN PATINDEX('%Device=%',XML_Val)>0 THEN SUBSTRING(XML_Val,PATINDEX('%Device=%',XML_Val)+7,CHARINDEX(' ',XML_Val,PATINDEX('%Device=%',XML_Val))-(PATINDEX('%Device=%',XML_Val)+7)) ELSE NULL END AS Device,
CASE WHEN PATINDEX('%Status=%',XML_Val)>0 THEN SUBSTRING(XML_Val,PATINDEX('%Status=%',XML_Val)+7,CHARINDEX(' ',XML_Val,PATINDEX('%Status=%',XML_Val))-(PATINDEX('%Status=%',XML_Val)+6)) ELSE NULL END AS [Status],
CASE WHEN PATINDEX('%RowID=%',XML_Val)>0 THEN SUBSTRING(XML_Val,PATINDEX('%RowID=%',XML_Val)+6,CHARINDEX(']',XML_Val,PATINDEX('%RowID=%',XML_Val))-(PATINDEX('%RowID=%',XML_Val)+6)) ELSE NULL END AS RowID,
...
FROM
(
SELECT t.ProcessDTim,
  t.SourceSystem,
  m.n.value('./Results[1]','varchar(1000)') AS XML_Val
  FROM (SELECT ProcessDTim,SourceSystem,CAST(ProcessData AS xml) AS ProcessData FROM #ProcessTracking)t
  CROSS APPLY t.ProcessData.nodes('/Process')m(n)
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-08-03 : 17:53:47

quote:
Originally posted by visakh16

see illustration below

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ahh...I didn't even consider parsing first. Interesting.

I'll give it a try and report back with my results.

Thanks so much again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 17:58:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-08-04 : 10:48:09

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi ,

I was able to use your suggestion with success. I am going to test out some things...based on our data volume to see which method works best...using some of the XML operations or simply parsing the xml data in the nvarchar column for this other set of data.

Oh, and i read your blog about the FileTables in Sql2012. Very interesting and I think that we'll find that feature very helpful. Thanks for the review/explanation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-04 : 11:42:36

quote:
Originally posted by dhw

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi ,

I was able to use your suggestion with success. I am going to test out some things...based on our data volume to see which method works best...using some of the XML operations or simply parsing the xml data in the nvarchar column for this other set of data.

Oh, and i read your blog about the FileTables in Sql2012. Very interesting and I think that we'll find that feature very helpful. Thanks for the review/explanation.

ok...that should do the trick. lemme know if you face any more issues

Thanks and let me know your feedback for future articles as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-08-04 : 12:02:55

Hey again...

one last (maybe) question...and it is in regards to the XML itself.

with that initial data i illustrated, i was able to use your code to "pluck out" the specific xml elements.

however, with this second set of data, i was not able to use something like:
value('./Results[1]@Device'....
to get the Device or other elements. is this because the XML itself is not well-formed? if it were more like:


<Results> Device="Server23-SQL" Status="Abort Error=PK Violation" ExceptionCode="14509" RowID="7d90e612-01fc-82e3-ad42-c3599b87d43b" </Results>

Would i then be able to get at the node or element within the xml?

thanks
- will

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-04 : 12:31:03

nope...its because in second case you had all the parts inside value part of xml itself. if it was like below


<Results Device="Server23-SQL" Status="Abort Error=PK Violation" ExceptionCode="14509" RowID="7d90e612-01fc-82e3-ad42-c3599b87d43b"> some value</Results>

you could have used like

value('./Results[1]/@Device')....

the one coming inside Node part are attributes and ones coming within between <Node></Node> tags are values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-08-04 : 13:41:16

quote:
Originally posted by visakh16

nope...its because in second case you had all the parts inside value part of xml itself. if it was like below

<Results Device="Server23-SQL" Status="Abort Error=PK Violation" ExceptionCode="14509" RowID="7d90e612-01fc-82e3-ad42-c3599b87d43b"> some value</Results>

you could have used like

value('./Results[1]/@Device')....

the one coming inside Node part are attributes and ones coming within between <Node></Node> tags are values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Knowing that helps. I can try and talk with some of the devs and see if they can modify their routines that are generating this xml.

thanks again for the tremendous help and education.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-04 : 14:37:01

quote:
Originally posted by dhw

quote:
Originally posted by visakh16

nope...its because in second case you had all the parts inside value part of xml itself. if it was like below

<Results Device="Server23-SQL" Status="Abort Error=PK Violation" ExceptionCode="14509" RowID="7d90e612-01fc-82e3-ad42-c3599b87d43b"> some value</Results>

you could have used like

value('./Results[1]/@Device')....

the one coming inside Node part are attributes and ones coming within between <Node></Node> tags are values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Knowing that helps. I can try and talk with some of the devs and see if they can modify their routines that are generating this xml.

thanks again for the tremendous help and education.

welcome

in any programming language it wont be difficult for them to create xml with data portions coming as attributes so you can suggest that change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources