Extract string from in between

• monilps

  SSCommitted

  Points: 1686

  More actions

  August 1, 2013 at 4:04 pm

  #296210

  Example:

  Column Data Type: varchar(max)

  Table Name: ins

  ColumnName: inscol

  Data in "inscol"

  For Example:

  Row #1:

  <?xml version="1.0"?>

  <!DOCTYPE ins PUBLIC "ins" "hsdbdj">

  <instructions>

  <format-template>${ND}

  </format-template>

  <node id="node-0" variable-name="item">

  <node-text>Ref To</node-text>

  </node>

  <node id="node-1" variable-name="ND">

  <node-text>ABC</node-text>

  </node>

  </instructions>

  Output needed:

  ABC

  Row #2:

  <?xml version="1.0"?>

  <!DOCTYPE ins PUBLIC "ins" "hsdbdj">

  <instructions>

  <format-template>${ND}

  </format-template>

  <node id="node-1" variable-name="ND">

  <node-text>XYZ</node-text>

  </node>

  </instructions>

  Output needed:

  XYZ

  Note: In row it contains char(13) and char(10).

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  August 1, 2013 at 4:40 pm

  #1638014

  When you are asking for help it is useful to provide easily usable sample data like this:

  declare @ins table(inscol xml);

  insert @ins(inscol)

  select convert(xml,'<?xml version="1.0"?>

  <!DOCTYPE ins PUBLIC "ins" "hsdbdj">

  <instructions>

  <format-template>${ND}

  </format-template>

  <node id="node-0" variable-name="item">

  <node-text>Ref To</node-text>

  </node>

  <node id="node-1" variable-name="ND">

  <node-text>ABC</node-text>

  </node>

  </instructions>',2) union all

  select convert(xml,'<?xml version="1.0"?>

  <!DOCTYPE ins PUBLIC "ins" "hsdbdj">

  <instructions>

  <format-template>${ND}

  </format-template>

  <node id="node-1" variable-name="ND">

  <node-text>XYZ</node-text>

  </node>

  </instructions>',2)

  Then it is simple for people to help, like this:

  select nd.value('.','nvarchar(512)') as NodeText

  from @ins

  cross apply inscol.nodes('/instructions/node[@variable-name="ND"]/node-text/text()') x(nd)

  If you provide a working script with sample data using the same datatypes you have in your system, we can provide more accurate answers, but I hope this gives you a starting point.

  Note that because of the DTD in your XML, you need to use CONVERT with style option 2, otherwise, rather helpfully you will get this error:

  Msg 6359, Level 16, State 1, Line 1

  Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

  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]