Retrieving values ​​from an XML field in MS-SQL Server 2008

Question

I am trying to extract four pieces of information from an MS-SQL Server 2008 database that is stored in a single XML field. This is the first time I have had to work with XML, so I have a bit of a problem, and that is why I only have the data that I am trying to extract. I tried using other messages to solve my problem, but obviously no luck.

The four pieces of information are first the “Project Manager”, then the “Value”, then the “Profit Center”, and then this value. The value from Profit Center will be used to connect between the two tables. The following is a sample of the XML data that is stored in this field.

<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <EntityPropertyOfString> <Name>Project Manager</Name> <Value>DBD</Value> </EntityPropertyOfString> <EntityPropertyOfString> <Name>Profit Center</Name> <Value>211</Value> </EntityPropertyOfString> </ArrayOfEntityPropertyOfString> 

So, in this example, I need to use the "Profit Center" value "211" to combine the two tables in the MS-SQL query. In the table, that information can be called "tblProfitCenter" and the field containing it is "prftData".

Here is a completed query that would do the same job if the data in "prftData" were not in XML, but instead was a normal integer field containing the profit center identifier and performing the connection.

  SELECT md.LName, md.FName, pc.ProfitCenterName FROM tblMainDataCenter md LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData 

This is for the project in which I work, and should be able to move beyond this. Normally, I would study XML to solve this problem, but time will not allow it. Until I had the opportunity to learn XML, I would appreciate any help.

Answer 1

You can use the value function in an XML column to retrieve data from XML as follows:

 SELECT col.value('(/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name="Profit Center"]/Value)[1]', 'int') FROM tbl 

if your table is named tbl and the XML column is named col .

The first argument is an XPath 1 expression, and the second is the destination data type. Note that these rows must be fixed rows in SQL Server and cannot be built dynamically e. d. by concatenating strings.

Answer 2

The following query will allow you to take the contents of xml and put them in a table format so that you can then perform the necessary sql operations on that table:

 Declare @xmlstring xml = '<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <EntityPropertyOfString> <Name>Project Manager</Name> <Value>DBD</Value> </EntityPropertyOfString> <EntityPropertyOfString> <Name>Profit Center</Name> <Value>211</Value> </EntityPropertyOfString> </ArrayOfEntityPropertyOfString>' select MainDataCenter.Col.value('(Name)[1]','varchar(max)') as Name ,MainDataCenter.Col.value('(Value)[1]','varchar(max)') as Value from @xmlstring.nodes('/ArrayOfEntityPropertyOfString/EntityPropertyOfString') as MainDataCenter(Col)