read - Problema de SQL OpenXML Multiple Tag

sp_xml_preparedocument

Question

Soy nuevo en la lectura de un archivo XML en una tabla a través de SQL Server Management Studio. Probablemente hay mejores maneras, pero me gustaría usar este enfoque.

Actualmente estoy leyendo en un archivo XML estándar de registros de personas. Una etiqueta <record> es el nivel más alto de cada fila de datos. Quiero leer todos los registros en filas separadas en mi tabla SQL.

Me he llevado bien hasta ahora utilizando el siguiente enfoque de la siguiente manera:

SELECT
        -- Record
        category, editor, entered, subcategory, uid, updated,
        -- Person
        first_name, last_name, ssn, ei, title, POSITION,
FROM OPENXML(@hDoc, ''records/record/person/names'')
WITH 
(
    -- Record
    category [varchar](100) ''../../@category'',
    editor [varchar](100) ''../../@editor'',
    entered Datetime ''../../@entered'',
    subcategory [varchar](100) ''../../@subcategory'',
    uid BIGINT ''../../@uid'',
    updated [varchar](100) ''../../@updated'',
    -- Person
    first_name [varchar](100) ''first_name'',
    last_name [varchar](100) ''last_name'',
    ssn [varchar](100) ''../@ssn'',
    ei [varchar](100) ''../@e-i'',
    title [varchar](100) ''../title'',
    Position [varchar](100) ''../position'',  
)

Sin embargo, este enfoque ha funcionado bien ya que los nombres de las etiquetas han sido únicos para cada registro / persona. El problema que tengo está dentro de la etiqueta <Person> Ahora tengo una etiqueta <Aliases> que contiene una lista de más de 1 etiquetas <Alias> test name </Alias> . Si uso el enfoque y la referencia anteriores ''../aliases'' , obtengo todos los elementos de Alias ​​como una fila de cadena larga mezclada. Si solo intento ''../aliases/alias'' SOLAMENTE, el primer elemento se devuelve por fila de registro. Si hubiera 10 elementos Alias ​​dentro del conjunto de etiquetas Alias, me gustaría que se devolvieran 10 filas, por ejemplo.

¿Hay una manera de especificar que cuando hay varias etiquetas del mismo nombre dentro de una etiqueta de nivel superior, devuélvalas todas y no solo una fila?

El siguiente es el bloque de ejemplo dentro del XML al que me refiero:

- <aliases>
  <alias>test 1</alias> 
  <alias>test 2</alias> 
  <alias>test 3</alias> 
  </aliases>

Me gustaría lo siguiente en la tabla de SQL:

Record               Aliases

Record 1             test 1
Record 1             test 2
Record 1             test 3
Record 2             test 4
Record 2             test 5

pero todo lo que obtengo es:

Record 1             test 1 
Record 2             test 4

Disculpas si no he explicado esto correctamente, cualquier ayuda sería muy apreciada.

Answer 1

Como indicó Antonio, usaría XQuery en lugar de Openxml. He adivinado su código fuente xml, y proporcioné el código para sus 2 consultas:

/* Please note I have used different cases than you to follow a standard (first letter capitialised for elements, lowercase for attributes.
   As xml is case sensitive, you may need to change the below code to suit the case of your data */

/* Bring your xml into an xml variable */

DECLARE @xml xml = ''
<Records>
    <Record category="category1" editor="editor1" entered="2015-01-01" subcategory="subcategory1" uid="100001" updated="updated1">
        <Person ssn="ssn1" e-i="ei1">
            <Title>Title1</Title>
            <Position>Position1</Position>
            <Names>
                <First_name>FirstName1</First_name>
                <Last_name>LastName1</Last_name>
                <Aliases>
                    <Alias>test1</Alias>
                    <Alias>test2</Alias>
                    <Alias>test3</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
    <Record category="category2" editor="editor2" entered="2015-01-02" subcategory="subcategory2" uid="100002" updated="updated2">
        <Person ssn="ssn2" e-i="ei2">
            <Title>Title2</Title>
            <Position>Position2</Position>
            <Names>
                <First_name>FirstName2</First_name>
                <Last_name>LastName2</Last_name>
                <Aliases>
                    <Alias>test4</Alias>
                    <Alias>test5</Alias>
                </Aliases>
            </Names>
        </Person>
    </Record>
</Records>''


/* The unary relationship of 1 element/attribute type per record in xpath */

SELECT T.rows.value(''@category[1]'', ''[varchar](100)'') AS Category
      ,T.rows.value(''@editor[1]'', ''[varchar](100)'') AS Editor
      ,T.rows.value(''@entered[1]'', ''[Datetime]'') AS Entered
      ,T.rows.value(''@subcategory[1]'', ''[varchar](100)'') AS Subcategory
      ,T.rows.value(''@uid[1]'', ''[bigint]'') AS [UID]
      ,T.rows.value(''@updated[1]'', ''[varchar](100)'') AS Updated
      ,T.rows.value(''(Person/Names/First_name)[1]'', ''[varchar](100)'') AS First_Name
      ,T.rows.value(''(Person/Names/Last_name)[1]'', ''[varchar](100)'') AS Last_Name
      ,T.rows.value(''(Person/@ssn)[1]'', ''[varchar](100)'') AS SSN
      ,T.rows.value(''(Person/@e-i)[1]'', ''[varchar](100)'') AS ei
      ,T.rows.value(''(Person/Title)[1]'', ''[varchar](100)'') AS Title
      ,T.rows.value(''(Person/Position)[1]'', ''[varchar](100)'') AS Position
FROM @xml.nodes(''/Records/Record'') T(rows)


/* Record to alias, one-to-many mapping */

SELECT T.rows.value(''../../../../@category[1]'', ''[varchar](100)'') AS Category
      ,T.rows.value(''.'', ''[varchar](100)'') AS Alias
FROM @xml.nodes(''/Records/Record/Person/Names/Aliases/Alias'') T(rows)

Answer 2

Cuando trabajo con archivos XML, hago lo siguiente para dividir los nodos en columnas:

Archivo XML que se sienta en una columna en una tabla con el siguiente formato:

<CustomData>
<Name>Shaun</Name>
<Surname>Johnson</Surname>
<Title>Mr</Title>
<Age>29</Age>
</CustomData>

La declaración Select que utilizo:

SELECT  
[ColumnName].value(''(/CustomData//Name/node())[1]'' , ''nvarchar(max)'') AS Name,
[ColumnName].value(''(/CustomData//Surname/node())[1]'',''nvarchar(max)'') AS Surname,
[ColumnName].value(''(/CustomData//Title/node())[1]'' , ''nvarchar(max)'') AS Title,
[ColumnName].value(''(/CustomData//Age/node())[1]'' , ''nvarchar(max)'') AS Age
FROM TableName

Resultados:

Name  Surname  Title  Age
----  -------  -----  ---
Shaun Johnson  Mr     29

Answer 3

Intente modificar la cláusula FROM de la siguiente manera:

SELECT
...

FROM OPENXML(@hDoc, ''records/record/person/aliases'')

Answer 4

openxml debe devolver un valor escalar, debe usar el método .nodes en XML unido a su OPENXML