SQL query XML datatype column

• gonzalezc82

  SSC Enthusiast

  Points: 140

  More actions

  December 28, 2010 at 4:07 pm

  #74146

  Hello,

  I have a SQL table that has one column with XML data type. Below are my columns in this table

  the last entry is the column with XML data type

  ObjectID

  Name

  Description

  GUID

  ClassType

  FieldID

  XmlEncodedLabels

  Below is the XML data, I need to extract <d2p1:Value> as an additional column

  <TranslateableLabel xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SoftwareHouse.CrossFire.Common.Shared">

  <BaseLanguage>en-US</BaseLanguage>

  <LabelDictionary xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">

  <d2p1:KeyValueOfstringstring>

  <d2p1:Key>en-US</d2p1:Key>

  <d2p1:Value>Lakeside Cleaning</d2p1:Value>

  </d2p1:KeyValueOfstringstring>

  </LabelDictionary>

  <LabelTag>Lakeside</LabelTag>

  </TranslateableLabel>

  I am complete newbie to using XML so any help would be greatly appreciated.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  December 28, 2010 at 5:38 pm

  #1266975

  Hi there, please take a look at this sample based on your supplied xml data and see if it makes sense and helps.

  -- set up some sample data using the supplied xml

  CREATE TABLE #test (XmlEncodedLabels xml)

  GO

  INSERT #test(XmlEncodedLabels)

  SELECT '<TranslateableLabel xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SoftwareHouse.CrossFire.Common.Shared">

  <BaseLanguage>en-US</BaseLanguage>

  <LabelDictionary xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">

  <d2p1:KeyValueOfstringstring>

  <d2p1:Key>en-US</d2p1:Key>

  <d2p1:Value>Lakeside Cleaning</d2p1:Value>

  </d2p1:KeyValueOfstringstring>

  </LabelDictionary>

  <LabelTag>Lakeside</LabelTag>

  </TranslateableLabel>'

  GO

  -- declare our xml namespaces as used in the xml fragment

  ;with xmlnamespaces (

  DEFAULT 'http://schemas.datacontract.org/2004/07/SoftwareHouse.CrossFire.Common.Shared' ,

  'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d2p1,

  'http://www.w3.org/2001/XMLSchema-instance' as i

  )

  -- and select the value required from the xml

  select XmlEncodedLabels.value('(TranslateableLabel/LabelDictionary/d2p1:KeyValueOfstringstring/d2p1:Value/text())[1]','varchar(100)') AS SomeXmlValue

  from #test

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]