Creating XML in SQL

• esilva002

  SSC Journeyman

  Points: 83

  More actions

  December 7, 2010 at 11:23 am

  #227870

  hey hey hey!

  I have code it gives me an xml file that looks like this(I also supply the code below):

  <?xml version="1.0" encoding="windows-1252"?>

  <Root>

  <ListPreDef>

  <bm TYPE="1" COLOR="-1">

  <DockNo>675303</DockNo>

  <Judge>Judge</Judge>

  <Plaintiff>TUCKER, DAVID P JR. 1</Plaintiff>

  <Plainman/>

  <Defense/>

  <Defendant>COX, CORETTA 1</Defendant>

  <CMNT>pro se with children</CMNT>

  </bm>

  <ListPreDef>

  </Root>

  Code:

  select '\\lawserver\recordcp\Judge1.xml' filename

  , (select '<?xml version="1.0" encoding="windows-1252"?>' + cast((select (

  select

  1 '@TYPE',

  -1 '@COLOR',

  *

  from tblBIS_ExportJudge1 for xml path('bm'), type

  ) as 'ListPreDef'

  for xml path('Root'), type) as varchar(max))) data

  Now my problem lies within the data. becuase I am looking for something more like this:

  <?xml version="1.0" encoding="windows-1252"?>

  <Root>

  <ListPreDef>

  <bm TYPE="1" COLOR="-1">

  <DOCKNO>

  <![CDATA[CRB1002061]]>

  </DOCKNO>

  <JUDGE>

  <![CDATA[MAC]]>

  </JUDGE>

  <PLAINTIFF>

  <![CDATA[]]>

  </PLAINTIFF>

  <PLAINMAN>

  <![CDATA[]]>

  </PLAINMAN>

  <DEFENSE>

  <![CDATA[]]>

  </DEFENSE>

  <DEFENDANT>

  <![CDATA[Acton, Heidi Y]]>

  </DEFENDANT>

  <CMNT>

  <![CDATA[]]>

  </CMNT>

  </bm>

  </ListPreDef>

  </Root>

  Here is my question how do get <![CDATA[]]> around my data? Do I have to add an xml tag?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  December 7, 2010 at 4:30 pm

  #1259863

  Try having a read of Jacob's article here : http://beyondrelational.com/blogs/jacob/archive/2010/05/12/for-xml-workshop-for-xml-explicit-and-cdata-xml-and-xmltext-attributes.aspx

  It explains how to do this with FOR XML EXPLICIT

  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]