xml到tsql select语句只返回一行

I need to build an table using an xml.

我需要使用xml构建一个表。

This is my xml:

这是我的xml:

<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>

My try:

我的尝试:

DECLARE @ixml INT,
@Param  VARCHAR(max)='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'


 EXEC sp_xml_preparedocument  @ixml OUTPUT, @Param  

    Select Child,ID
    FROM OPENXML(@ixml, 'root')
    WITH 
    (
        Child [nVARCHAR](max) 'Child',  
        ID [INT] 'ID'
    ) 


----------

Actual output :

实际输出:

Child  |    ID
c1     |    9908

Expected Output:

预期的输出:

Child  |    ID
c1     |    9908
c2     |    9908
c3     |    9908
c4     |    9908

can anyone help me?

谁能帮我吗?

2 个解决方案

#1

Try this:

试试这个:

DECLARE @ixml INT,
@Param  VARCHAR(max)='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'


 EXEC sp_xml_preparedocument  @ixml OUTPUT, @Param  

Select Child, ID
FROM OPENXML(@ixml, '/root/Child')
WITH 
(
    Child [nVARCHAR](max) '.', ID [int]   '../ID'
)

#2

Starting with declaring the @Param as an XML type, and using XPath expressions in nodes and value, you would get:

首先声明@Param为XML类型，并在节点和值中使用XPath表达式，您将得到:

DECLARE @Param XML='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'

SELECT
    Child=n.v.value('.[1]','NVARCHAR(128)'),
    ID=n.v.value('../ID[1]','INT')
FROM
    @Param.nodes('root/Child') AS n(v);

With the expected result.

与预期的结果。

A slightly better version (with input from @Shnugo), with direct selection of /root/ID instead of backward navigation in the XPath expression:

稍微好一点的版本(带有@Shnugo的输入)，直接选择/root/ID，而不是在XPath表达式中向后导航:

SELECT
    Child=n.v.value('.[1]','NVARCHAR(128)'),
    ID=@Param.value('(/root/ID/text())[1]','INT')
FROM
    @Param.nodes('root/Child') AS n(v);

#1