CODEWITHSUNDEEP
phpsqlsql-serverxml
Renny
Renny

Reputation: 29

Pulling one value from XML in MSSQL

I have the following XML all contained in one cell in a MSSQL DB:

<receipt reference="INT000003" major="2" minor="14.804" beta="">

<dates id="Booking Dates">    
<item id="Date of Booking">23 May 2013 - 17:09</item>     
</dates>   

<advertisement id="advertisement details">
<item id="Booked by">Mr System Administrator</item>
<item id="Brand or Product">Testing</item>
<item id="Priority">500</item>
</advertisement>

</receipt>

I want to get the value <item id="Brand or Product">Testing</item> out of that cell (specifically "Testing" though) to use in a PHP based webpage. Does anyone know of any PHP to read this type of XML and search for values?

Thanks.

Upvotes: 0

Views: 136

Answers (2)

Ravi Singh
Ravi Singh

Reputation: 2080

This can also done in SQL as below :

declare @xml xml = 
'<receipt reference="INT000003" major="2" minor="14.804" beta="">

<dates id="Booking Dates">    
<item id="Date of Booking">23 May 2013 - 17:09</item>     
</dates>   

<advertisement id="advertisement details">
<item id="Booked by">Mr System Administrator</item>
<item id="Brand or Product">Testing</item>
<item id="Priority">500</item>
</advertisement>

</receipt>'  

select @xml.value
   ('(/receipt/advertisement/item[@id="Brand or Product"])[1]', 'nvarchar(100)')

Also, you can directly fetch the value from table as below :

select cast(YOUR_COLUMN_NAME as XML).value('(/receipt/advertisement/item[@id="Brand or Product"])[1]', 'nvarchar(100)') 
as s from YOUR_TABLE_NAME

Upvotes: 1

Hugo Delsing
Hugo Delsing

Reputation: 14173

You can use DOMXPath::query for it in PHP. First select the entire XML from the table as you normally would and then get the correct elements.

<?php
$xml = <<<END
<receipt reference="INT000003" major="2" minor="14.804" beta="">
  <dates id="Booking Dates">    
    <item id="Date of Booking">23 May 2013 - 17:09</item>     
  </dates>   

  <advertisement id="advertisement details">
    <item id="Booked by">Mr System Administrator</item>
    <item id="Brand or Product">Testing</item>
    <item id="Priority">500</item>
  </advertisement>

</receipt>
END; //you would load it from the table

$dom = new DOMDocument;
$dom->loadXML($xml);
$xpath = new DOMXPath($dom);
$query = "//advertisement/item";

$items = $xpath->query($query);

foreach ($items as $item) {
  echo $item->nodeValue;
}
?>

This will output

Mr System Administrator
Testing
500

And if you would need the value of a specific ID you can use

<?php
$dom = new DOMDocument;
$dom->loadXML($xml);
$xpath = new DOMXPath($dom);
$query = "//advertisement/item[@id='Brand or Product']";

$items = $xpath->query($query);

foreach ($items as $item) {
  echo $item->nodeValue;
}

Output:

Testing

Upvotes: 1

Related Questions