SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Nvarchar column containing XML		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 07/24/2012 :  14:20:27  Show Profile  Reply with Quote
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

India
47121 Posts
Posted - 07/24/2012 :  14:39:24  Show Profile  Reply with Quote
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/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 07/24/2012 :  16:09:48  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 07/24/2012 :  16:18:21  Show Profile  Reply with Quote
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/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 08/03/2012 :  14:20:10  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 08/03/2012 :  15:23:11  Show Profile  Reply with Quote
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/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 08/03/2012 :  17:53:47  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 08/03/2012 :  17:58:03  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 08/04/2012 :  10:48:09  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 08/04/2012 :  11:42:36  Show Profile  Reply with Quote
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/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 08/04/2012 :  12:02:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 08/04/2012 :  12:31:03  Show Profile  Reply with Quote
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/
Go to Top of Page

dhw
Constraint Violating Yak Guru

USA
324 Posts
Posted - 08/04/2012 :  13:41:16  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47121 Posts
Posted - 08/04/2012 :  14:37:01  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000