问题

以下是您要求的翻译：

"I've a table with a blob column containing xml data in hex.

Example table:

When I just query it like so:

SELECT SAVED, CUST_ID, XML_DATA
FROM FOO_TABLE
WHERE CUST_ID = 1234

I get the following:

Now if I take the Hex, minus the starting x' and ending ' and plug it into a [converter][1] I get the following:

<?xml version="1.0" encoding="UTF-8"?>
<notes version="1">
	<labels>
		<label id="0" color="30DBFF">Label 1</label>"

希望这能帮助您解决DB2中的转换问题。

<details>
<summary>英文:</summary>

I&#39;ve a table with a blob column containing xml data in hex.

Example table:

| Column | Datatype |
| -------- | -------------- |
| Saved    | timestamp          |
| Cust_id   | int           |
| XML_Date   | blob(16777216)            |

When I just query it like so:

SELECT SAVED, CUST_ID, XML_DATA
FROM FOO_TABLE
WHERE CUST_ID = 1234

I get the following:

| Saved | Cust_id |XML_data |
| -------- | -------------- |-------------- |
| 2023-06-07    | 1234           |x'3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C6E6F7465732076657273696F6E3D2231223E0A093C6C6162656C733E0A09093C6C6162656C2069643D22302220636F6C6F723D22333044424646223E4C6162656C20313C2F6C6162656C3E'            |

Now if I take the Hex, minus the starting x' and ending ' and plug it into a [converter][1] I get the following: 

<?xml version="1.0" encoding="UTF-8"?>
<notes version="1">
<labels>
<label id="0" color="30DBFF">Label 1</label>

The aim is to do this in DB2. I've tried multiple ways to no avail, even enlisted chatGPT.

select cast(hex(xml_data) as varchar(1000))as cnv_xml

Results in:

SQL0171N The statement was not processed because the data type, length or

value of the argument for the parameter in position "1" of routine

"SYSIBM.HEX" is incorrect. Parameter name: "". SQLSTATE=42815

then 

SELECT TRANSLATE(
CAST(XML_DATA AS VARCHAR(1000) CCSID UNICODE),
'0123456789ABCDEF',
x'0123456789ABCDEF'
) as CNV_XML

gives

SQL0622N The clause "CCSID UNICODE" is invalid for this database.

SQLSTATE=56031

Finally, one that runs but just spits back the original HEX

SELECT TRANSLATE(
CAST(XML_DATA AS VARCHAR(1000) FOR BIT DATA),
'0123456789ABCDEF',
x'0123456789ABCDEF'
) as CNV_XML

I'm pretty sure I don't have the rights to create functions or change the explain table unicode settings.

Is there a way to convert these blobs or am I SOOL?
Thanks.

  [1]: https://www.rapidtables.com/convert/number/hex-to-ascii.html

</details>


# 答案1
**得分**: 1

您的数据不以十六进制存储，它只是纯文本。
看起来是这样的，因为这是二进制数据的显示方式。
您可以将其转换为varchar以查看其显示为文本。不要使用HEX或Translate，utf-8的ccsid是1208。

SELECT SAVED，CUST_ID，CAST(XML_DATA AS VARCHAR(32764))
FROM FOO_TABLE
WHERE CUST_ID = 1234

<details>
<summary>英文:</summary>

Your data is not stored in hex, its just plain text.  
It looks like that because that is how blob data is displayed.  
You can convert it to varchar to see it displayed as text. Do not use HEX or Translate, also the ccsid for utf-8 is 1208.  

    SELECT SAVED, CUST_ID, CAST(XML_DATA AS VARCHAR(32764))
           FROM FOO_TABLE
           WHERE CUST_ID = 1234 

</details>



# 答案2
**得分**: 0

你必须真正拥有一个XML文档的BLOB表示，存储在BLOB字段中。不仅仅是一些"类似XML内容的二进制字符串"。

也就是说，如果`XMLPARSE (DOCUMENT XML_DATA)`不返回正确的XML文档，你需要自行处理XML_DATA内容，以使其像下面一样工作。

示例中的XML文档的字符串表示已转换为BLOB，以复制您的XML_DATA列内容。请注意，您的"转换器输出"显示了不正确的文档 - 没有尾随的`</labels></notes>`标签。然后，它使用`XMLPARSE`进行解析，并使用`XMLSERIALIZE`转换为CLOB。

您可以使用`XMLTABLE`函数来获取所需的XML文档中的一些元素或属性。

```sql
WITH FOO_TABLE (SAVED, CUST_ID, XML_DATA) AS (
VALUES 
(
  '2023-06-07'::DATE
, 1234
, CAST ('<?xml version="1.0" encoding="UTF-8"?>
<notes version="1">
<labels>
        <label id="0" color="30DBFF">Label 1</label>
</labels>
</notes>' AS BLOB (1K))
)
)
SELECT 
  T.SAVED
, T.CUST_ID
-- XML as CLOB
, XMLSERIALIZE (XMLPARSE (DOCUMENT XML_DATA) AS CLOB (1K))
    AS CNV_XML
-- XML elements & attributes as relational columns	
, X.LABEL_ELEMENT
, X.LABEL_ID
, X.LABEL_COLOR
, X.NOTES_VERSION
FROM 
  FOO_TABLE T
-- If you want to get XML elements & attributes
, XMLTABLE 
('$D/notes/labels/label' PASSING XMLPARSE (DOCUMENT XML_DATA) AS "D"
  COLUMNS
    LABEL_ELEMENT    VARCHAR (128)    PATH '.'
  , LABEL_ID        INT               PATH '@id'
  , LABEL_COLOR     VARCHAR (128)    PATH '@color'
  , NOTES_VERSION   INT               PATH '../../@version'
) X

WITH FOO_TABLE (SAVED, CUST_ID, XML_DATA) AS (
VALUES 
(
  '2023-06-07'::DATE
, 1234
, CAST ('<?xml version="1.0" encoding="UTF-8"?>
<notes version="1">
<labels>
        <label id="0" color="30DBFF">Label 1</label>
</labels>
</notes>'
AS BLOB (1K))
)
)
SELECT 
  T.SAVED
, T.CUST_ID
-- XML as CLOB
, XMLSERIALIZE (XMLPARSE (DOCUMENT XML_DATA) AS CLOB (1K))
	AS CNV_XML
-- XML elements & attributes as relational columns	
, X.LABEL_ELEMENT
, X.LABEL_ID
, X.LABEL_COLOR
, X.NOTES_VERSION
FROM 
  FOO_TABLE T
-- If you want to get XML elements & attributes
, XMLTABLE 
('$D/notes/labels/label' PASSING XMLPARSE (DOCUMENT XML_DATA) AS "D"
  COLUMNS
    LABEL_ELEMENT	VARCHAR (128)	PATH '.'
  , LABEL_ID		INT				PATH '@id'
  , LABEL_COLOR		VARCHAR (128)	PATH '@color'
  , NOTES_VERSION	INT				PATH '../../@version'
) X