Update Value in XML Node Joining From a Table

• sivaram

  SSC Journeyman

  Points: 97

  More actions

  April 10, 2008 at 12:56 pm

  #184354

  I have an XML response string.

  I need to query a table and fetch Desc for Id 1 and replace it with AAAA. Can I join this XML with Desctable and fetch message directly ?

  Desc Table

  1 Absent

  2 Failed Min Mark

  Final string is like this

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  April 11, 2008 at 2:47 am

  #801349

  Look up sql:column and sql:variable in Books Online.

  For a more accurate answer, please, provide a sample query (including table DDL and sample data) and a sample of the XML.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com

• mmilodragovich

  SSC Veteran

  Points: 282

  More actions

  December 13, 2011 at 2:47 pm

  #1420910

  I think I have a very similar need:

  a) I have an "input" xml document that carries the input specifications for multiple tests:

  declare @xmlInput xml

  set @xmlInput = '<Tests>

  <Test>

  <Id>1</Id>

  <Name>Test1</Name>

  <Inputs>

  <Input1>17</Input1>

  <Input2>18</Input2>

  </Inputs>

  </Test>

  <Test>

  <Id>2</Id>

  <Name>Test2</Name>

  <Inputs>

  <Input1>47</Input1>

  <Input2>48</Input2>

  </Inputs>

  </Test>

  </Tests>'

  select @xmlinput

  b) The tests are run and the results for each test are written to a table:

  create table TestResults (RequestId int, Results xml)

  insert TestResults values (1,'<Results><a>101</a><b>102</b></Results>')

  insert TestResults values (2,'<Results><a>201</a><b>202</b></Results>')

  select * from TestResults

  c) Now I need to create a third process that merges the results into the original test specification document. I need the results to look like this:

  <Tests>

  <Test>

  <Id>1</Id>

  <Name>Test1</Name>

  <Inputs>

  <Input1>17</Input1>

  <Input2>18</Input2>

  </Inputs>

  <Results>

  <a>101</a>

  <b>102</b>

  </Results>

  </Test>

  <Test>

  <Id>2</Id>

  <Name>Test2</Name>

  <Inputs>

  <Input1>47</Input1>

  <Input2>48</Input2>

  </Inputs>

  <Results>

  <a>201</a>

  <b>202</b>

  </Results>

  </Test>

  </Tests>

  So somehow I need to join the original document with the results table on "test id" and insert a new "Results" node for each test. Any help would be appreciated.

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  December 13, 2011 at 3:25 pm

  #1420919

  Try this:

  declare@resultxml

  set@result

  = (

  selectTests.Test.query('Id')

  ,Tests.Test.query('Name')

  ,Tests.Test.query('Inputs')

  ,TestResults.Results.query('child::*')

  from@xmlinput.nodes

  ('

  /Tests/Test

  ') Tests (Test)

  inner joinTestResults

  onTestResults.RequestId = Tests.Test.query('Id').value('.', 'int')

  for xml path('Test'), root('Tests'), type

  )

  select@result

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com

• mmilodragovich

  SSC Veteran

  Points: 282

  More actions

  December 13, 2011 at 5:19 pm

  #1420949

  YES!!!!!

  This does the trick. Thanks so much, I hope it addresses the original question as well.

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  December 14, 2011 at 12:52 am

  #1421056

  Who knows.

  The OP never came back with a sample as comprehensive as yours.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com