XML out of SQL Question

[fergmj]

We are using SQL Server 2005 and we want to get XML out of it.

The two problems are how to get it formatted correctly. it doesn't look good coming out of SQL

and how do you get the beginning tag of
<?xml version="1.0" encoding="utf-8" ?>

  <?xml version="1.0" encoding="utf-8" ?> 
- <MSG>
  <DOCTYPE>3DAY_SHIPPINGINFO</DOCTYPE> 
- <POS>
  <SHIPDATE>06/01/2008</SHIPDATE> 
- <!--  Date Product was shipped 
  --> 
- <PO>
- <!--  Individual PO/Order shipped
  --> 
- <HEADER>
  <ORDERNUMBER>14031350</ORDERNUMBER> 
- <!--  3 Day Blinds Invoice Number
  --> 
  <ORDERDATE>05/13/2008</ORDERDATE> 
- <!--  Invoice Date 
  --> 
  <STORENUMBER>140</STORENUMBER> 
- <!--  3DB Store 
  --> 
  <STORENAME>5129 HOUSTON GALLERIA</STORENAME> 
  <PRODUCTCOUNT>2</PRODUCTCOUNT> 
- <!--  Total Boxes used to ship order
  --> 
  </HEADER>
- <PRODUCTS>
- <PRODUCT>
- <!--  Box Detail 
  --> 
  <DESCRIPTION>NA</DESCRIPTION> 
- <!--  No Box Name or Description available 
  --> 
  <TRACKINGNUMBER>123456789</TRACKINGNUMBER> 
- <!--  Tracking Number 
  --> 
  <SHIPPINGCOMPANY>FED EX</SHIPPINGCOMPANY> 
- <!--  Company shipping product 
  --> 
  <ITEMCOUNT>2</ITEMCOUNT> 
- <!-- Total items in this box
  --> 
- <ITEMS>
- <ITEM>
  <TDBSERIALNO>1680100</TDBSERIALNO> 
- <!--  3 Day Blinds production serial.  Uniquely identifies product
  --> 
  <DESCRIPTION>Transition 2" Wood Blind</DESCRIPTION> 
  <WIDTHWHOLENUMBER>23</WIDTHWHOLENUMBER> 
  <WIDTHFRACTION>1/2</WIDTHFRACTION> 
  <LENGTHWHOLENUMBER>54</LENGTHWHOLENUMBER> 
  <LENGTHFRACTION /> 
  <ROOM>Master Bedroom - msr</ROOM> 
  <COLOR>Painted/Smooth -White</COLOR> 
  <MOUNT>Inside</MOUNT> 
  <CONTROL>Cord Tilt Right/Pull Left</CONTROL> 
  </ITEM>
- <ITEM>
  <TDBSERIALNO>1680101</TDBSERIALNO> 
  <DESCRIPTION>Transition 2" Wood Blind</DESCRIPTION> 
  <WIDTHWHOLENUMBER>23</WIDTHWHOLENUMBER> 
  <WIDTHFRACTION>1/2</WIDTHFRACTION> 
  <LENGTHWHOLENUMBER>54</LENGTHWHOLENUMBER> 
  <LENGTHFRACTION /> 
  <ROOM>Master Bedroom - msr</ROOM> 
  <COLOR>Painted/Smooth -White</COLOR> 
  <MOUNT>Inside</MOUNT> 
  <CONTROL>Cord Tilt Right/Pull Left</CONTROL> 
  </ITEM>
  </ITEMS>
  </PRODUCT>
- <PRODUCT>
  <DESCRIPTION>NA</DESCRIPTION> 
  <TRACKINGNUMBER>987654321</TRACKINGNUMBER> 
  <SHIPPINGCOMPANY>UPS</SHIPPINGCOMPANY> 
  <ITEMCOUNT>1</ITEMCOUNT> 
- <ITEMS>
- <ITEM>
  <TDBSERIALNO>1680100</TDBSERIALNO> 
  <DESCRIPTION>Transition 2" Wood Blind</DESCRIPTION> 
  <WIDTHWHOLENUMBER>23</WIDTHWHOLENUMBER> 
  <WIDTHFRACTION>1/2</WIDTHFRACTION> 
  <LENGTHWHOLENUMBER>54</LENGTHWHOLENUMBER> 
  <LENGTHFRACTION /> 
  <ROOM>Master Bedroom - msr</ROOM> 
  <COLOR>Painted/Smooth -White</COLOR> 
  <MOUNT>Inside</MOUNT> 
  <CONTROL>Cord Tilt Right/Pull Left</CONTROL> 
  </ITEM>
  </ITEMS>
  </PRODUCT>
  </PRODUCTS>
  </PO>
  </POS>
  </MSG>

 

[chiph]

it doesn't look good coming out of SQL

Looking good has no importance.
The important thing is whether it will correctly parse.

Recall that you can have XML all on one super-long line, and it's still valid.

Also note, using IE as your XML viewer is a bad idea -- notice that you have all the -/+ marks in there? Invest in a decent XML editor, like XMLSpy, or even Notepad++

Chip H.


____________________________________________________________________
www.chipholland.com

 

[fergmj]

but how can I get the beginning tag out of SQL?

<?xml version="1.0" encoding="utf-8" ?>

 

[fergmj]

Nevermind I found an answer

 

[philhege]

And folks who come in here looking for that answer will find your thread and wonder how you did it.

It's good form to post solutions if you have one and if the submitter merits the assistance (which in this case is you, so there's merit, right?).

Phil H.
Some Bank
-----------
Time's fun when you're having flies.