Columna faltante de análisis XML

[Syndicate_Admin]

Hola a todos, y gracias de antemano por cualquier ayuda que puedan brindarnos.

Soy bastante nuevo en Power BI y tengo problemas para analizar una columna XML determinada. El XML es:

<dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0" xmlns:s="http://niem.gov/niem/structures/2.0">
<dot-ec:Vehicle s:id="vehicle1">
<nc:VehicleColorPrimaryCode>BLU</nc:VehicleColorPrimaryCode>
<nc:ItemMakeName>VOLK</nc:ItemMakeName>
<nc:ItemModelName>JETTA</nc:ItemModelName>
<nc:ItemModelYearDate>2011</nc:ItemModelYearDate>
<nc:VehicleCMVIndicator>false</nc:VehicleCMVIndicator>
<nc:ConveyanceRegistration>
<nc:ConveyanceRegistrationPlateIdentification>
<nc:ID>184ZM4</nc:ID>
<nc:IdentificationExpirationDate>
<nc:Date>2020-04-30</nc:Date>
</nc:IdentificationExpirationDate>
<j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code>
<nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code>
</nc:ConveyanceRegistrationPlateIdentification>
<dot-ec:VehiclePlateTypeCode>PAN</dot-ec:VehiclePlateTypeCode>
<dot-ec:VehiclePlateTypeText>Passenger Normal</dot-ec:VehiclePlateTypeText>
</nc:TransporteRegistro>
<dot-ec:VehiclePassenger16PlusIndicator>false</dot-ec:VehiclePassenger16PlusIndicator>
<dot-ec:HazMatPlacardDisplayIndicator>false</dot-ec:HazMatPlacardDisplayIndicator>
<dot-ec:VehicleRegistrationUnknownIndicator>false</dot-ec:VehicleRegistrationUnknownIndicator>
<nc:InsuranceCarrierName>GOVT EMPLOYEE INS</nc:InsuranceCarrierName>
</dot-ec:Vehículo>
</dcjis:DataExchange>

Al analizar y expandir el XML de una tabla de SQL Server en Power BI, se excluye la columna VehiclePlateTypeText. La tabla de SQL Server contiene tres columnas:

• MSPPSDataID
• CitaciónNo
• Vehículo (XML)

Sin embargo, si guardo el XML solo en un archivo de texto y lo cargo en Power BI, se analiza y expande correctamente para incluir la columna VehiclePlateTypeText. Esta no es una solución útil, ya que tengo que extraer el XML de SQL Server.

¿Alguna idea de por qué sucede esto? ¿Alguien sabe si es posible obligar a Power BI a obtener todos los datos XML? Gracias.

[Syndicate_Admin]

Encontré una solución al problema. Aunque ninguna de las respuestas proporcionó una solución, este hilo se puede cerrar.

[Syndicate_Admin]

Hola @johnjbolduc ,

Gracias por sus comentarios. Si es conveniente, ¿podría compartir su solución aquí? Esto sería muy útil para otros miembros de la comunidad que podrían encontrarse con un problema similar. Agradecemos su contribución.

Saludos

[Syndicate_Admin · Accepted solution]

Debido a que he tenido tan poca suerte tratando de analizar mi XML con SQL Server XQUERY, y Power BI no hace un muy buen trabajo, actualmente estoy usando un modelo híbrido, donde analizo algunos de los datos XML en SQL usando funciones de cadena (sí, lo sé, pero usas lo que sabes; ), y uso Power BI para los fragmentos que maneja razonablemente bien.

Aquí está la cosa. Cuando Power BI analiza una columna XML, aparentemente lo hace en función del contenido del primer registro que encuentra. Dado que el contenido XML varía de un registro a otro, y no todos los puntos de datos están necesariamente en cada columna XML, a menos que el XML del primer registro contenga todos los puntos de datos, la tabla creada estará incompleta. Para evitar esto, necesitaba una columna XML que contuviera todos los puntos de datos relevantes.

• Primero eliminé una etiqueta de imagen que puede existir al final de la columna XML (si existe, abarca más del 75% del contenido, es irrelevante y puede conducir a un XML incompleto o incorrecto).
• Después de quitar la imagen, agregué las etiquetas finales apropiadas para garantizar que XML sea válido.
• En este punto, Power BI no puede analizar este XML complejo, por lo que divido el XML en 14 fragmentos más pequeños mediante agrupaciones de datos lógicos.
• Se agregaron etiquetas finales adecuadas para garantizar que XML sea válido.
• Se editaron manualmente las 14 columnas XML según fuera necesario para un solo registro con MSPPSDataId = 1, lo que garantiza que existan todos los puntos de datos. Tenga en cuenta que solo MSPPSDataId = 1 existe en las tablas.
• Importe las 14 tablas en Power BI y transforme los datos, analice XML y expanda todas las tablas.
• Finalmente, actualicé la base de datos con todos los registros de datos y todo está bien.

Se necesitó mucho ensayo y error para que todas las piezas funcionaran, y está lejos de ser elegante, pero me proporciona una forma repetible de obtener todos los datos en un archivo .pbix de referencia.

[Syndicate_Admin]

Solucioné el problema quitando la columna CitationNo de la tabla. Después de todo, puede que no necesite esa columna más adelante.

[Syndicate_Admin]

Vale, se está mostrando mi ignorancia de Power BI. No tengo idea de cómo usar el archivo de texto.

[Syndicate_Admin]

Hola @johnjbolduc

El archivo de texto tiene dos consultas. Cada uno comienza con //nombre de consulta. Cree una consulta en blanco en el editor de consultas, elimine todo lo que contenga y, a continuación, pegue una consulta en ella. Verá un ejemplo de transformación.

[Syndicate_Admin]

Hola @johnjbolduc

Intenté tratar su XML como una columna en lugar de como una fuente de datos independiente y pude obtener los datos de VehiclePlateTypeCode

Consulte las consultas de ejemplo en el pbix adjunto.

[Syndicate_Admin]

Hola danextion -

Gracias por la rápida respuesta. Desafortunadamente, tengo que actualizar Power BI Desktop, ya que mi versión (V2.138.1452.0) no puede cargar su archivo pbix. No estoy seguro de qué tan rápido puedo obtener la actualización, ya que tiene que pasar por la TI central.

[Syndicate_Admin]

Consulte el archivo de texto adjunto.