Cześć, coś nie mogę sparsować xml-a z przestrzenią
Mianowicie wygląda tak :
<Product>
<p1:CoverPrice xmlns:p1="http:......">34.0</p1:CoverPrice>
<p2:Vat xmlns:p2="http:......">23</p2:Vat>
<p3:preview_exists xmlns:p3="http:......">false</p3:preview_exists>
<p4:SaleNotRestricted xmlns:p4="http:......" />
<p5:HyphenatedISBN xmlns:p5="http:......">978-83-7278-961-7</p5:HyphenatedISBN>
<p6:excerpts xmlns:p6="http:......">
<p6:excerpt md5="9bb19b3c183cda627c5e57a0520de" file_size="1134132" file_type="epub_excerpt" updated_at="2014-05-05T14:36:11+02:00" id="24731">http:......</p6:excerpt>
<p6:excerpt md5="84eb8c493316541084941926b2e0" file_size="1319845" file_type="mobi_excerpt" updated_at="2014-05-05T14:35:39+02:00" id="24730">http:......</p6:excerpt>
</p6:excerpts>
<p7:masters xmlns:p7="http:......">
<p7:master id="24729" md5="293bd68394b47ca44f88a2b65ebb5" file_size="2729221" file_type="mobi" updated_at="2014-05-05T14:34:56+02:00" />
<p7:master id="24728" md5="c052fb62f1b0d75ee72616db44a8" file_size="1442374" file_type="epub" updated_at="2014-05-05T14:33:27+02:00" />
</p7:masters>
</Product>
a mój select wygląda tak :
SELECT std_dodatki_xml = (
SELECT 1 "url/@typ"
,REPLACE(x.value('.[1]/@file_type', 'VARCHAR(20)'), '_excerpt', '') "url/@format"
,x.value('.[1]/@file_size', 'INT') "url/@file_size"
,x.value('.[1]/@md5', 'NVARCHAR(255)') "url/@etag"
,x.value('.[1]/@id', 'INT') "url/@internal_file_id"
,x.value('.[1]/@id', 'VARCHAR(20)') + '.' + REPLACE(x.value('.[1]/@file_type', 'VARCHAR(20)'), '_excerpt', '') "url/@orginal_file_name"
,x.value('.[1]', 'VARCHAR(512)') "url"
FROM a.xml_data.nodes('/Product/p6:excerpts/p6:excerpt') AS tx(x)
FOR XML PATH(''), ROOT('src'), TYPE)
FROM import.dbo.elibri_p_xml a
jednak błąd z sql-a to który to zwraca to :
XQuery [import.dbo.elibri_p_xml.xml_data.nodes()]: The name "p6" does not denote a namespace.